package com.cmict.data.form.api.service.impl;

import cn.hutool.core.util.ObjectUtil;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.cmict.data.bpm.api.instance.BpmProcessInstanceApi;
import com.cmict.data.bpm.api.instance.dto.BpmProcessInstanceStatusDTO;
import com.cmict.data.form.api.constant.FormConstants;
import com.cmict.data.form.api.entity.SysDownload;
import com.cmict.data.form.api.entity.SysTableColumn;
import com.cmict.data.form.api.entity.dataobject.SysFormDO;
import com.cmict.data.form.api.entity.vo.*;
import com.cmict.data.form.api.enums.DefaultNumber;
import com.cmict.data.form.api.mapper.DynamicMapper;
import com.cmict.data.form.api.mapper.SysCurrencyMapper;
import com.cmict.data.form.api.mapper.SysFormMapper;
import com.cmict.data.form.api.service.SysCurrencyService;
import com.cmict.data.form.api.service.SysFormService;
import com.cmict.data.form.api.task.CurrencyDataApi;
import com.cmict.data.form.api.task.FormDataApi;
import com.cmict.data.form.api.task.dto.SysFromSeachDataVoDTO;
import com.cmict.data.form.api.util.*;
import com.cmict.data.framework.common.pojo.CommonResult;
import com.cmict.data.infra.api.rdb.dto.SourceDTO;
import com.cmict.data.system.api.sql.SqlParameterCheck;
import com.cmict.data.framework.security.core.util.SecurityFrameworkUtils;
import com.cmict.data.framework.tenant.core.aop.TenantIgnore;
import com.cmict.data.infra.api.rdb.RdbApi;
import com.cmict.data.system.api.dict.DictDataApi;
import com.cmict.data.system.api.encodedetails.dict.EnCodeDataApi;
import com.cmict.data.system.api.encodedetails.dict.dto.EncodeDetailsDTO;
import com.cmict.data.system.api.user.AdminUserApi;
import com.cmict.data.system.api.user.dto.AdminUserRespDTO;
import com.google.common.collect.Lists;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.POIXMLDocumentPart;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.atomic.AtomicReference;
import java.util.stream.Collectors;

import static com.cmict.data.framework.common.pojo.CommonResult.error;
import static com.cmict.data.framework.common.pojo.CommonResult.success;

/**
 * 文件夹表 服务实现类
 *
 * @Author qiaoyu
 * @since 2021-8-6
 */
@Service("sysCurrencyService")
@RequiredArgsConstructor
@Slf4j
public class SysCurrencyServiceImpl extends ServiceImpl<SysCurrencyMapper, Object> implements SysCurrencyService, DefaultNumber, FormConstants {

    private static final Logger logger = LoggerFactory.getLogger(SysCurrencyServiceImpl.class);
    private final SysCurrencyMapper sysCurrencyMapper;
    private final SysFormMapper sysFormMapper;

    @Autowired
    private AdminUserApi adminUserService;

    @Resource
    private SysFormService sysFormService;

    @Resource
    private DictDataApi dictDataApi;

    @Resource
    private AdminUserApi adminUserApi;

    @Resource
    private SysCurrencyService sysCurrencyService;

    @Resource
    private EnCodeDataApi enCodeDataApi;
    @Resource
    private FormDataApi formDataApi;
    @Resource
    private CurrencyDataApi currencyDataApi;
    @Resource
    private BpmProcessInstanceApi processInstanceApi;

    @Resource
    private DynamicMapper dynamicMapper;

    @Autowired
    private RdbApi rdbApiService;


    @Value("${platform.number.row}")
    private int platformRowNumber;

    @Value("${platform.number.cell}")
    private int platformCellNumber;

    @Value("${spring.datasource.dynamic.datasource.slave.name}")
    private String tableSchema;

    @Value("${spring.datasource.dynamic.datasource.master.name}")
    private String tableMaster;

    @Value("${platform.fileupload.upload-folder}")
    private String uploadFolder;

    @Override
    public CommonResult isAdaptationOldData() {
        List<SysFormDO> sysFormList = sysFormService.selectListAll();
        String sqlList = "select table_name from information_schema.tables where table_schema='"+tableSchema+"';";
        List<String> tableNameLIst = sysCurrencyService.getTableNameList(sqlList);
        for (SysFormDO sysForm : sysFormList){
            if(tableNameLIst.contains(sysForm.getTableName())){
                List<String> columnList = getColumnName(sysForm.getTableName());
                if(!columnList.contains("data_id") && !columnList.contains("version_id") && !columnList.contains("approve_status")){
                    String sql = "ALTER TABLE " + tableSchema + "." + sysForm.getTableName() +" ADD data_id bigint(20) COMMENT '数据主键id' ,ADD version_id INT(3) DEFAULT '0' COMMENT '版本号' ,ADD approve_status TINYINT(4) DEFAULT '0' COMMENT '状态'";
                    sysCurrencyMapper.upData(sql);
                    logger.info("表："+ sysForm.getTableName() +"增加字段成功");
                    String sqll = "UPDATE " + tableSchema + "." + sysForm.getTableName() +" SET data_id = id";
                    sysCurrencyMapper.upData(sqll);
                    logger.info("表："+ sysForm.getTableName() +"修改data_id成功");
                }
            }
        }
        return CommonResult.success( "老数据适配成功",true);
    }

    @Override
    public List<String> getTableNameList(String sql) {
        return sysCurrencyMapper.getTableNameList(sql);
    }

    @Override
    @TenantIgnore
    public CommonResult createFormTableAll(SysFormTableAllVo sysFormTableAllVo) {
        List<Long> ids = new ArrayList<>();
        //主表数据表结构
        if (ObjectUtil.isNotEmpty(sysFormTableAllVo.getSysFromVo())){
            try {
                return createTable(sysFormTableAllVo.getSysFromVo());
            }catch (Exception e){
                return error(500,"主表数据库表创建失败");
            }
        }else if(ObjectUtil.isNotEmpty(sysFormTableAllVo.getFormDO())){
            SysFormDO formDO = sysFormTableAllVo.getFormDO();
            SysFormDO sysFormDO = sysFormService.selectOne(formDO.getTableName());
            if(ObjectUtil.isNotEmpty(sysFormDO)){
                return error(500,"创建失败,表单已存在");
            }
            try {
                return success(sysFormService.createForm(formDO));
            }catch (Exception e){
                return error(500,"主表数据库表创建失败");
            }
        }else if(ObjectUtil.isNotEmpty(sysFormTableAllVo.getSysFromVoList())){
            for (SysFromTableVo vo:sysFormTableAllVo.getSysFromVoList()){
                try {
                    return success(sysCurrencyService.createTable(vo));
                }catch (Exception e){
                    return error(500,"子表数据库表创建失败");
                }
            }
        }else if(ObjectUtil.isNotEmpty(sysFormTableAllVo.getFormDOList())){
            for(SysFormDO sysFormDO : sysFormTableAllVo.getFormDOList()){
                SysFormDO sysForm = sysFormService.selectOne(sysFormDO.getTableName());
                if(ObjectUtil.isNotEmpty(sysForm)){
                    return success("创建失败,表单已存在");
                }
                try {
                    Long id = sysFormService.createForm(sysFormDO);
                    ids.add(id);
                }catch (Exception e){
                    return error(500,"子表数据库表创建失败");
                }
            }
        }else{
            return error(500,"参数信息有误请检查");
        }
        return success("创建成功",ids);
    }

    @Override
    @TenantIgnore
    public CommonResult editFormTableAll(SysFormTableAllVo sysFormTableAllVo) {
        List<Long> ids = new ArrayList<>();
        //主表数据表结构
        if (ObjectUtil.isNotEmpty(sysFormTableAllVo.getSysFromVo())){
            try {
                return createTable(sysFormTableAllVo.getSysFromVo());
            }catch (Exception e){
                return error(500,"主表数据库表创建失败");
            }
        }else if(ObjectUtil.isNotEmpty(sysFormTableAllVo.getFormDO())){
            SysFormDO sysForm = sysFormService.selectOne(sysFormTableAllVo.getFormDO().getTableName());
            if(ObjectUtil.isNotEmpty(sysForm) && !(String.valueOf(sysForm.getId()).equals(String.valueOf(sysFormTableAllVo.getFormDO().getId())))){
                return success("更新失败,表单已存在");
            }
            try {
                sysFormService.updateForm(sysFormTableAllVo.getFormDO());
            }catch (Exception e){
                return error(500,"主表数据库表修改失败");
            }
        }else if(ObjectUtil.isNotEmpty(sysFormTableAllVo.getSysFromVoList())){
            for (SysFromTableVo vo:sysFormTableAllVo.getSysFromVoList()){
                try {
                    return success(sysCurrencyService.createTable(vo));
                }catch (Exception e){
                    return error(500,"子表数据库表创建失败");
                }
            }
        }else if(ObjectUtil.isNotEmpty(sysFormTableAllVo.getFormDOList())){
            for (int i = 0; i <sysFormTableAllVo.getFormDOList().size() ; i++) {
                SysFormDO sysForm = sysFormService.selectOneByTableName(sysFormTableAllVo.getFormDOList().get(i).getTableName());
                if(ObjectUtil.isNotEmpty(sysForm)){
                    try {
                        sysFormService.updateForm(sysFormTableAllVo.getFormDOList().get(i));
                    }catch (Exception e){
                        return error(500,"子表数据库表创建失败");
                    }
                }else{
                    return error(500,"修改失败,表单不存在");
                }
            }
        }else{
            return error(500,"参数信息有误请检查");
        }
        return success("创建成功",ids);
    }

    @Override
    @TenantIgnore
    public CommonResult addColumnTableAll(SysFormTableAllVo sysFormTableAllVo) {
        List<Long> ids = new ArrayList<>();
        //表数据表结构集合
        if (ObjectUtil.isNotEmpty(sysFormTableAllVo.getSysFromVoList())){
            for (int i = 0; i <sysFormTableAllVo.getSysFromVoList().size() ; i++) {
                try {
                    return addcColumnTable(sysFormTableAllVo.getSysFromVoList().get(i));
                }catch (Exception e){
                    return error(500,"主表数据库表创建失败");
                }
            }
        }else if(ObjectUtil.isNotEmpty(sysFormTableAllVo.getFormDOList())){
            for (int i = 0; i <sysFormTableAllVo.getFormDOList().size() ; i++) {
                SysFormDO sysForm = sysFormService.selectOneByTableName(sysFormTableAllVo.getFormDOList().get(i).getTableName());
                if(ObjectUtil.isNotEmpty(sysForm)){
                    try {
                        sysFormService.updateForm(sysFormTableAllVo.getFormDOList().get(i));
                    }catch (Exception e){
                        return error(500,"子表数据库表创建失败");
                    }
                }else{
                    return error(500,"修改失败,表单不存在");
                }
            }
        }else{
            return error(500,"参数信息有误请检查");
        }
        return success("创建成功",ids);
    }

    @Override
    @TenantIgnore
    public CommonResult createTable(SysFromTableVo sysFromTableVo) {
        List<SysFromTableColumnVo> sysFromTableColumnList= sysFromTableVo.getData();
        int allsize = 0;
        if(ObjectUtil.isNotEmpty(sysFromTableColumnList)){
            for (int i = 0; i <sysFromTableColumnList.size() ; i++) {
                SysFromTableColumnVo vo = sysFromTableColumnList.get(i);
                if(vo.getFormat().equals("varchar")){
                    allsize = allsize + Integer.parseInt(vo.getLength());
                }
            }
        }
        if(allsize > 20000){
            return CommonResult.error(500,"字段总长度过长，请适当调整字段长度");
        }
        int id = 0;
        if(ObjectUtil.isNotEmpty(sysFromTableVo.getIsRestructure()) && ObjectUtil.isNotEmpty(sysFromTableVo.getTableNameOld())){
            String tableName = sysFromTableVo.getTableNameOld().toLowerCase();
            String sqlId = "SELECT id FROM " + tableSchema + "." + tableName + " ORDER BY id desc LIMIT 0,1";
            if(ObjectUtil.isNotEmpty(sysCurrencyMapper.getDataIdOrderBy(sqlId))){
                id = Integer.parseInt(sysCurrencyMapper.getDataIdOrderBy(sqlId)) +1;
            }
            String sql = "DROP TABLE IF EXISTS " + tableSchema + "." + tableName +"";
            sysCurrencyMapper.getDataIdOrderBy(sql);
        }
        String sqlList = "select table_name from information_schema.tables where table_schema='"+tableSchema+"';";
        List<String> tableNameLIst = sysCurrencyService.getTableNameList(sqlList);
        if(tableNameLIst.contains(sysFromTableVo.getTableName())){
            return CommonResult.success("数据表已存在！");
        }
        String sql = "CREATE TABLE ";
        String tableName = sysFromTableVo.getTableName().toLowerCase();
        if(ObjectUtil.isNotEmpty(tableName)){
            sql = sql + tableSchema + "." + tableName   +" (";
        }else{
            return CommonResult.success("表名不能为空!");
        }
        sql = sql + "id bigint(20) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '表单id'";
        Result r = SqlUtil.tableFieldSplicUtil(sysFromTableColumnList,sql);
        if(ObjectUtil.isEmpty(r.getData())){
            return CommonResult.error(500,r.getMsg());
        }
        sql = r.getData() + ", data_id bigint(20) COMMENT '数据主键id', version_id INT(3) DEFAULT '0' COMMENT '版本号', approve_status TINYINT(4) DEFAULT '0' COMMENT '状态', creator bigint(20) COMMENT '创建者', data_type varchar(20) COMMENT '1-用户，2-管理', create_time datetime(0) COMMENT '创建时间', update_time datetime(0) COMMENT '创建时间',tenant_id int(20) COMMENT '租户关联' ,parent_id bigint(20) COMMENT '主表单关联id' ,relation_id bigint(20) COMMENT '上级数据id') AUTO_INCREMENT = "+ id +"";
        if(sql.length() > 8100){
            sql = sql + " engine=MyISAM";
        }
        //sql = r.getData() + ", creator bigint(20) COMMENT '创建者', data_type varchar(20) COMMENT '1-用户，2-管理', create_time datetime(0) COMMENT '创建时间', update_time datetime(0) COMMENT '创建时间',tenant_id int(20) COMMENT '租户关联' ,parent_id bigint(20) COMMENT '主表单关联id' ,relation_id bigint(20) COMMENT '上级数据id',PRIMARY KEY (`id`) USING BTREE) AUTO_INCREMENT = "+ id +"";
        logger.info("sql:   "+sql);
        try {
            sysCurrencyMapper.createData(sql);
        }catch (Exception e){
            return error(500,"创建数据表失败，请检查参数是否正确");
        }
        return CommonResult.success("创建成功",true);
    }

    @Override
    public CommonResult addcColumnTable(SysFromTableVo sysFromTableVo) {
        List<SysFromTableColumnVo> SysFromTableColumnList= sysFromTableVo.getData();
        String tableName = sysFromTableVo.getTableName();
        List<String> columnList = sysCurrencyService.getColumnName(tableName);
        for (int i = 0; i < SysFromTableColumnList.size(); i++) {
            SysFromTableColumnVo sysFromTableColumnVo = SysFromTableColumnList.get(i);
            if (ObjectUtil.isNotEmpty(sysFromTableColumnVo.getIsTagChange()) || columnList.contains(sysFromTableColumnVo.getName())) {
                String sqldelete = "ALTER TABLE " + tableSchema + "." + tableName + " DROP IF EXISTS" + sysFromTableColumnVo.getName();
                logger.info("先删除字段 - " + sqldelete);
                try {
                    sysCurrencyMapper.createData(sqldelete);
                }catch (Exception e){
                    return error(500,"表字段不存在，无法删除");
                }
            }
        }
        String sql = "ALTER TABLE " + tableSchema + ".";
        if(ObjectUtil.isNotEmpty(tableName)){
            sql = sql + tableName;
        }else{
            return CommonResult.success("表名不能为空!");
        }
        Result r = SqlUtil.addTableColumnUtil(SysFromTableColumnList,sql);
        sql = r.getData().toString();
        logger.info("sql:   "+sql);
        try {
            sysCurrencyMapper.createData(sql);
        }catch (Exception e){
            return error(500,"表字段已经存在，无法添加");
        }
        return CommonResult.success( "创建成功",true);
    }

    @Override
    public CommonResult deleteColumnTable(String tableName, String columnName) {
        String sql = "ALTER TABLE " + tableSchema + "." + tableName + " DROP " +columnName;
        try {
            sysCurrencyMapper.createData(sql);
        }catch (Exception e){
            return error(500,"表字段不存在，无法删除");
        }
        return CommonResult.success( "删除成功",true);
    }

    @Override
    public CommonResult insertData(SysFromInsertDataVo sysFromInsertDataVo) {
        if(ObjectUtil.isNotEmpty(sysFromInsertDataVo.getRepeatVerifyList())) {
            List<String> fieldNames = dataRepeatCheck(sysFromInsertDataVo,null);
            if(ObjectUtil.isNotEmpty(fieldNames)){
                return CommonResult.success(PARAMETER_MSG,fieldNames);
            }
        }
        CommonResult commonResult = intLengVerif(sysFromInsertDataVo);
        if (commonResult.isError()){
            return commonResult;
        }
        List<String> tableNameList = getTableName();
        String tableName = sysFromInsertDataVo.getTableName();
        if(!tableNameList.contains(tableName.toLowerCase())){
            return CommonResult.error(500,"表已不存在，请重新建表");
        }
        String sql = "INSERT INTO ";
        if(ObjectUtil.isNotEmpty(tableName)){
            sql = sql + tableSchema + "." + tableName;
        }else{
            return CommonResult.success("表名不能为空!");
        }
        sql = SqlUtil.insertIntoData(sysFromInsertDataVo,sql);
        logger.info("sql:   "+sql);
        try {
            sysCurrencyMapper.insertData(sql);
        }catch (Exception e){
            return error(500,"数据插入失败，请检查传参");
        }
        String idSql = "SELECT MAX(id) from "+ tableSchema + "." +tableName+"";
        String id = sysCurrencyMapper.getInsertDataId(idSql);
        return CommonResult.success("写入成功", id);
    }

    @Override
    public CommonResult insertDataChild(SysFromInsertDataVo sysFromInsertDataVo) {
        if(ObjectUtil.isNotEmpty(sysFromInsertDataVo.getRepeatVerifyList())) {
            List<String> fieldNames = dataRepeatCheck(sysFromInsertDataVo,null);
            if(ObjectUtil.isNotEmpty(fieldNames)){
                return CommonResult.success(PARAMETER_MSG,fieldNames);
            }
        }
        List<String> tableNameList = getTableName();
        String tableName = sysFromInsertDataVo.getTableName();
        if(!tableNameList.contains(tableName)){
            CommonResult.error(500,"表已不存在，请重新建表");
        }
        String sql = "INSERT INTO ";
        if(ObjectUtil.isNotEmpty(tableName)){
            sql = sql + tableSchema + "." + tableName;
        }else{
            CommonResult.error(500,"表名不能为空");
        }
        sql = SqlUtil.insertIntoData(sysFromInsertDataVo,sql);
        logger.info("sql:   "+sql);
        try {
            sysCurrencyMapper.insertData(sql);
        }catch (Exception e){
            return error(500,"数据插入失败，请检查传参");
        }
        String idSql = "SELECT MAX(id) from "+ tableSchema + "." +tableName+"";
        String id = sysCurrencyMapper.getInsertDataId(idSql);
        return CommonResult.success("写入成功", id);
    }


    @Override
    public CommonResult excelInsertData(SysFromInsertDataVo sysFromInsertDataVo) {
       /* CommonResult commonResult = intLengVerif(sysFromInsertDataVo);
        if (commonResult.isError()){
            return commonResult;
        }*/
        String sql = "INSERT INTO ";
        String tableName = sysFromInsertDataVo.getTableName();
        if(ObjectUtil.isNotEmpty(tableName)){
            sql = sql + tableName;
        }else{
            return CommonResult.success("表名不能为空!");
        }
        sql = SqlUtil.excelInsertIntoData(sysFromInsertDataVo,sql);
        logger.info("sql:   "+sql);
        try {
            sysCurrencyMapper.insertData(sql);
        }catch (Exception e){
            return error(500,"数据插入失败，请检查传参");
        }
       /* String idSql = "SELECT MAX(id) from " +tableName+"";
        String id = sysCurrencyMapper.getInsertDataId(idSql);*/
        return CommonResult.success("写入成功", null);
    }

    @Override
    public CommonResult upData(SysFromUpDataVo sysFromUpDataVo) {
        if(ObjectUtil.isNotEmpty(sysFromUpDataVo.getRepeatVerifyList())){
            List<String> fieldNames = dataRepeatCheck(null,sysFromUpDataVo);
            if(ObjectUtil.isNotEmpty(fieldNames)){
                return CommonResult.success(PARAMETER_MSG,fieldNames);
            }
        }
        CommonResult commonResult = intLengVerifUp(sysFromUpDataVo);
        if (commonResult.isError()){
            return commonResult;
        }
        String sql = "UPDATE ";
        String tableName  = sysFromUpDataVo.getTableName();
        String id  = sysFromUpDataVo.getDataId();
        if(ObjectUtil.isNotEmpty(tableName)){
            sql = sql + tableSchema + "." + tableName + " SET ";
        }else{
            return CommonResult.success("数据id不能为空!");
        }
        sql = SqlUtil.upData(sysFromUpDataVo,sql,id);
        logger.info("sql:   "+sql);
        try {
            sysCurrencyMapper.upData(sql);
        }catch (Exception e){
            return error(500,"数据修改失败，请检查传参");
        }
        return CommonResult.success("写入成功",true);
    }

    @Override
    public CommonResult upDataList(List<SysFromUpDataVo> sysFromUpDataVoList , String parentId,List<String> repeatVerifyList) {
        //修改子表单数据 - 全部删除 然后新建
        if(ObjectUtil.isNotEmpty(sysFromUpDataVoList)){
            for (int i = 0; i <sysFromUpDataVoList.size() ; i++) {
                //String sql = "TRUNCATE TABLE  "+ sysFromUpDataVoList.get(i).getTableName();
                String sql = "DELETE FROM  " + tableSchema + "." + sysFromUpDataVoList.get(i).getTableName() + " WHERE parent_id='" +sysFromUpDataVoList.get(i).getDataId() + "'";
                sysCurrencyService.deleteDataAll(sql);
            }
        }
        for (int i = 0; i <sysFromUpDataVoList.size() ; i++) {
            SysFromUpDataVo sysFromUpDataVo = sysFromUpDataVoList.get(i);
            sysFromUpDataVo.setRepeatVerifyList(repeatVerifyList);
            if(ObjectUtil.isNotEmpty(sysFromUpDataVo.getRepeatVerifyList())){
                List<String> fieldNames = dataRepeatCheck(null,sysFromUpDataVo);
                if(ObjectUtil.isNotEmpty(fieldNames)){
                    return CommonResult.success(PARAMETER_MSG,fieldNames);
                }
            }
            SysFromInsertDataVo sysFromInsertDataVo = new SysFromInsertDataVo();
            BeanUtils.copyProperties(sysFromUpDataVo,sysFromInsertDataVo);
            SysFromDataColumnVo vodate = new SysFromDataColumnVo();
            vodate.setValue(sysFromUpDataVo.getDataId());
            vodate.setName("parent_id");
            vodate.setType("bigint");
            sysFromUpDataVo.getData().add(vodate);
            String sqlin = "INSERT INTO "  + tableSchema + "." + sysFromUpDataVo.getTableName();
            sqlin = SqlUtil.insertIntoData(sysFromInsertDataVo,sqlin);
            logger.info("sql:   "+sqlin);
            try {
                sysCurrencyMapper.insertData(sqlin);
            }catch (Exception e){
                return error(500,"数据修改失败，请检查传参");
            }
        }
        return CommonResult.success("写入成功",true);
    }

    public void deleteData(String sql) { sysCurrencyMapper.deleteData(sql);
    }

    @Override
    public void deleteDataAll(String sql) { sysCurrencyMapper.deleteDataAll(sql);
    }

    @Override
    public CommonResult getFormDataAndStatus(Long loginUserId, SysFromSeachDataVoDTO sysFromSeachData) {
        String sql = "select table_name from information_schema.tables where table_schema='"+tableSchema+"';";
        List<String> tableNameLIst = getTableNameList(sql);
        Long formId = null;
        if(StringUtils.isNotEmpty(sysFromSeachData.getTableName())){
            SqlParameterCheck.checkMatch(sysFromSeachData.getTableName());
        }
        if(tableNameLIst.contains(sysFromSeachData.getTableName())){
            // 根据表名获取表单id
            try {
                formId = formDataApi.getFormIdByTableName(sysFromSeachData.getTableName());
            }catch (Exception e){
                return error(500,"数据库已经不存在，请重新创建");
            }
            CommonResult result= currencyDataApi.selectData(sysFromSeachData);
            if(!processInstanceApi.bindProcess(formId)){
                String groupField = sysFromSeachData.getGroupField();
                if(StringUtils.isNoneBlank(groupField)){
                    return  groupByCode(result,groupField);
                }
                return result;
            }
            JSONObject data = (JSONObject) JSON.toJSON(result.getData());
            if(data.get("size") == null) {
                JSONArray records = data.getJSONArray("records");
                setProcessInstance(formId, records);
                result.setData(records);
                String groupField = sysFromSeachData.getGroupField();
                if(StringUtils.isNoneBlank(groupField)){
                    return groupByCode(result,groupField);
                }
            } else {
                JSONArray records = data.getJSONArray("records");
                setProcessInstance(formId, records);
                result.setData(data);
                String groupField = sysFromSeachData.getGroupField();
                if(StringUtils.isNoneBlank(groupField)){
                    return    groupByCode(result,groupField);
                }
            }
            return result;
        }
        return success("数据库表名不存在！",null);
    }

    private void setProcessInstance(Long formId, JSONArray records) {
        for (Object o : records) {
            JSONObject record = (JSONObject) o;
            CommonResult<BpmProcessInstanceStatusDTO> instanceStatus = processInstanceApi.getInstanceStatus(formId.toString(), record.get("id").toString());
            BpmProcessInstanceStatusDTO checkedData = instanceStatus.getCheckedData();
            if (checkedData != null) {
                record.put("result", checkedData.getResult() == null ? 4 : checkedData.getResult());
                record.put("processInstanceId", checkedData.getProcessInstanceId());
                record.put("formStatus", checkedData.getFormStatus());
                record.put("processDefinitionId", checkedData.getProcessDefinitionId());
            }
        }
    }

    /**
     * 分组算法
     * @param result
     * @param groupField
     * @return
     */
    private CommonResult groupByCode(CommonResult result, String groupField) {
        JSONObject data = (JSONObject) JSON.toJSON(result.getData());
        JSONArray records = data.getJSONArray("records");
        Map<String, List<Object>> dataMap = new TreeMap<>();
        List<Object> list = new ArrayList<>();
        //数据分组算法
        for (Object o : records) {
            JSONObject json = JSONObject.parseObject(o.toString());
            List<Object> tempList = (List<Object>) dataMap.get(json.get(groupField) !=null ? json.get(groupField).toString() : json.get(groupField));
            if(tempList == null) { //说明是第一次，则创建list
                tempList = new ArrayList<>();
                tempList.add(json);
                dataMap.put(json.get(groupField).toString(), tempList);//放入map中
            }else {
                tempList.add(json);
            }
        }
        //遍历
        int i =1;
        for (String group : dataMap.keySet()) {
            i++;
            Map<String, Object> map = new HashMap<>();
            map.put("groupField",group);
            map.put("id","a"+ i);
            map.put("parentId",0);
            List<Object> datalist = dataMap.get(group);
            List<Object> resultList = new ArrayList<>();
            if(datalist!=null && datalist.size() >0 ){
                for (Object o : datalist) {
                    JSONObject json = JSONObject.parseObject(o.toString());
                    json.put("parentId","a"+ i);
                    resultList.add(json);
                }
            }
            map.put("children",resultList);
            list.add(map);
        }
        data.put("records",list);
        result.setData(data);
        return result;
    }

    @Override
    public Map<Object, Object> getDataOne(String sql) {
        logger.info("sql:   "+sql);
        return sysCurrencyMapper.getDataOne(sql);
    }

    public CommonResult selectData(SysFromSeachDataVo sysFromSeachDataVo) {
        SysFormDO sysFormDO = sysFormMapper.selectOneByTableName(sysFromSeachDataVo.getTableName());
        if(ObjectUtil.isEmpty(sysFormDO)){
            return CommonResult.error(500,"表单已经不存在，请重新创建");
        }
        //Boolean run = AssemblyTypeUtil.treeList(sysFormDO.getFormJsonMessage());
        Map<Object,Object> childSeachData = new HashMap<>();
        SysFromSeachDataVo sysFromSeachData = new SysFromSeachDataVo();
        //分解查询条件
        groupSearchColumn(sysFromSeachDataVo, childSeachData, sysFromSeachData);
        Page<Vector> page = new Page(1,10);
        if(ObjectUtil.isNotEmpty(sysFromSeachData.getCurrent()) && ObjectUtil.isNotEmpty(sysFromSeachData.getSize())){
            sysFromSeachData.setCurrent((sysFromSeachData.getCurrent()-1)*sysFromSeachData.getSize());
            sysFromSeachData.setSize(sysFromSeachData.getSize());
            page.setCurrent((sysFromSeachData.getCurrent())*sysFromSeachData.getSize());
            page.setSize(sysFromSeachData.getSize());
        }else{
            sysFromSeachData.setCurrent(0);
            sysFromSeachData.setSize(10);
            page.setCurrent(sysFromSeachData.getCurrent());
            page.setSize(sysFromSeachData.getSize());
        }
        String tableName = sysFromSeachData.getTableName();
        List<String> sysTableColumn = sysCurrencyService.getColumnName(tableName);
        String sql = "";
        if(sysTableColumn.contains("data_id")){
            //新数据（增加流程版本）
            sql = "SELECT  MAX(sp.id)  FROM " + tableSchema + "."+ tableName + " sp INNER JOIN " + tableMaster + ".system_users sm  ON sp.`creator`= sm.id GROUP BY IFNULL(sp.data_id,UUID_SHORT()) ";
        }else{
            //适配老数据表用
            sql = "SELECT  sp.id  FROM " + tableSchema + "."+ tableName + " sp INNER JOIN " + tableMaster + ".system_users sm  ON sp.`creator`= sm.id";
        }
        logger.info("sql:   "+sql);
        List<String> idList = adminUserApi.selectData2(sql).getCheckedData();
//        List<String> idList =  sysCurrencyMapper.selectData2(sql);
        if(ObjectUtil.isNotEmpty(sysFromSeachDataVo.getSeachData())){
            idList = selectDataAndChild(sysFromSeachDataVo,idList);
        }
        String formStatusAndResult = sysFromSeachData.getFormStatusAndResult();
        if(ObjectUtil.isNotEmpty(formStatusAndResult)) {
            List<String> bpmFormRecordIdList = new ArrayList<>();
            for (String id : idList) {
                CommonResult<BpmProcessInstanceStatusDTO> instanceStatus = processInstanceApi.getInstanceStatus(sysFormDO.getId().toString(), id);
                if (instanceStatus.getCode() != 500) {
                    BpmProcessInstanceStatusDTO checkedData = instanceStatus.getCheckedData();
                    if (checkedData != null) {
                        List<String> formStatusAndResultList = formStatusAndResult.contains("-") ? Arrays.asList(formStatusAndResult.split("-")) : new ArrayList<>();
                        if (formStatusAndResultList.size() > 0) {
                            int formStatus = Integer.valueOf(formStatusAndResultList.get(0));
                            int result = Integer.valueOf(formStatusAndResultList.get(1));
                            checkedData.setFormStatus(checkedData.getFormStatus() == null ? 0 : checkedData.getFormStatus());
                            checkedData.setResult(checkedData.getResult() == null ? 4 : checkedData.getResult());
                            if (checkedData.getFormStatus() !=null && checkedData.getFormStatus() == formStatus && checkedData.getResult()!=null && checkedData.getResult() == result) {
                                bpmFormRecordIdList.add(id);
                            }
                        }
                    }
                }
            }
            if (formStatusAndResult.contains("-")) {
                idList = bpmFormRecordIdList; //状态筛选过滤，重新赋值
            }
        }
        String join = StringUtils.join(idList, ',');
        String sql2 = null;
        if(StringUtils.isNoneBlank(join)){
            sql2 = "SELECT  * from " + tableName + " WHERE 1 = 1 and id in ( " + join + " ) ";
        }else{
            sql2 = "SELECT  * from " + tableName + " WHERE 1 = 1 and id in ( " + 0 + " ) ";
        }
        //拼接搜索前，把时间补充全
        sysFromSeachData = DateChangeUtil.dateMapping(sysFormDO,sysFromSeachData);
        //字段搜索拼接
        sql2 = SqlUtil.jsonSeachData(sysFromSeachData,sql2);
        //时间区间搜索拼接
        sql2 = SqlUtil.jsonSeachDate(sysFromSeachData,sql2);
        //排序拼接
        sql2 = SqlUtil.jsonOederBy(sysFromSeachData,sql2);
        logger.info("查询sql----  " + sql2);
        ArrayList total = (ArrayList) sysCurrencyMapper.selectData(sql2);
        //分页拼接
        logger.info("是否分页  " + sysFromSeachDataVo.getIsPage());
        if(ObjectUtil.isEmpty(sysFromSeachDataVo.getIsPage())){
            sql2 = SqlUtil.page(sql2, sysFromSeachData);
        }
        if(ObjectUtil.isNotEmpty(sysFromSeachDataVo.getIsPage())) {
            if(sysFromSeachDataVo.getIsPage()){
                sql2 = SqlUtil.page(sql2, sysFromSeachData);
            }
        }
        logger.info("查询sql********  " + sql2);
        //ArrayList data = (ArrayList) sysCurrencyMapper.selectData(sql2);
        List<Map<String,Object>> dataMapList = sysCurrencyMapper.selectData(sql2);
        List<Map<String,Object>> dataMapList2 = sysCurrencyMapper.selectData(sql2);
        dataMapList.forEach(item->{
            item.forEach((k,v)->{
                if (v instanceof BigDecimal || v instanceof Double) {
                    item.put(k, String.valueOf(v));
                }
            });
        });
        //TODO 1. 可否直接分页查询
        //子表单插入
        SysFormDO sysForm = sysFormService.selectOneByTableName(tableName); //主表

        List<SysFormDO> sysFormList = sysFormService.getChildSysFormByIds(sysForm.getId()); //字表
        List<SysFromSeachDataVo> selectChildSeachCloum = new ArrayList<>();
        if(ObjectUtil.isNotEmpty(childSeachData)){
            selectChildSeachCloum = selectChildSeachCloum(sysFromSeachDataVo);
        }
        long start = System.currentTimeMillis();
        List<SysFromSeachDataVo> finalSelectChildSeachCloum = selectChildSeachCloum;
        List<Map<String,Object>> dataMapListReturn = new ArrayList<>();
        if (ObjectUtil.isNotEmpty(sysFormList)) {
            for (int i = 0; i <dataMapList.size() ; i++) {
                Map<String,Object> item = dataMapList.get(i);
                Set<Map<String,Object>> childTables = new HashSet<>();
                Set<Map<String,Object>> childTablesStore = new HashSet<>();
                for (SysFormDO formDO : sysFormList) {
                    if (ObjectUtil.isNotEmpty(finalSelectChildSeachCloum)) {
                        for (SysFromSeachDataVo fromSearchDataVo : finalSelectChildSeachCloum) {
                            if (formDO.getTableName().equals(fromSearchDataVo.getTableName())) {
                                Long id = (Long) item.get("data_id");
                                String sqlChildSeach = "SELECT  * from " + formDO.getTableName() + " WHERE parent_id = " + id + " and version_id = (select max(version_id) from " + formDO.getTableName() + " where parent_id = " + id + ")";
                                //字段搜索拼接
                                sqlChildSeach = SqlUtil.jsonSeachData(fromSearchDataVo, sqlChildSeach);
                                List<Map<String, Object>> childDataMapList = sysCurrencyMapper.selectData(sqlChildSeach);
                                childDataMapList.forEach(itemc->{
                                    itemc.forEach((k,v)->{
                                        if (v instanceof BigDecimal || v instanceof Double) {
                                            itemc.put(k, String.valueOf(v));
                                        }
                                    });
                                });
                                List<Map<String, Object>> childDataMapList2 = sysCurrencyMapper.selectData(sqlChildSeach);
                                List<Map<String, Object>> data = AssemblyTypeUtil.associatedDataMapping(formDO.getTableName(), childDataMapList, null).getData();
                                Map<String,Object> childTable = new HashMap<>();
                                childTable.put("tableName", formDO.getTableName());
                                childTable.put("data", JSON.toJSONString(data));
                                childTables.add(childTable);
                                Map<String,Object> childTableStore = new HashMap<>();
                                childTableStore.put("tableName", formDO.getTableName());
                                childTableStore.put("data", childDataMapList2);
                                childTablesStore.add(childTable);
                                if(ObjectUtil.isNotEmpty(childDataMapList)){
                                    item.put("childTables", childTables);
                                    item.put("childTableStore", childTablesStore);
                                    dataMapListReturn.add(item);
                                }
                            }
                        }
                    } else {
                        Long id = (Long) item.get("data_id");
                        String sqlChild = "SELECT  * from " + formDO.getTableName() + " WHERE parent_id = " + id + " and version_id = (select max(version_id) from " + formDO.getTableName() + " where parent_id = " + id + ")";
                        List<Map<String, Object>> childDataMapList = sysCurrencyMapper.selectData(sqlChild);
                        childDataMapList.forEach(itemc->{
                            itemc.forEach((k,v)->{
                                if (v instanceof BigDecimal || v instanceof Double) {
                                    itemc.put(k, String.valueOf(v));
                                }
                            });
                        });
                        List<Map<String, Object>> childDataMapList2 = sysCurrencyMapper.selectData(sqlChild);
                        List<Map<String, Object>> data = AssemblyTypeUtil.associatedDataMapping(formDO.getTableName(), childDataMapList, null).getData();
                        Map<String,Object> childTable = new HashMap<>();
                        childTable.put("tableName", formDO.getTableName());
                        childTable.put("data", JSON.toJSONString(data));
                        childTables.add(childTable);
                        Map<String,Object> childTableStore = new HashMap<>();
                        childTableStore.put("tableName", formDO.getTableName());
                        childTableStore.put("data", childDataMapList2);
                        childTablesStore.add(childTableStore);
                        item.put("childTables", childTables);
                        item.put("childTableStore", childTablesStore);
                        dataMapListReturn.add(item);
                    }
                }
            }
        }else{
            dataMapListReturn = dataMapList;
        }
        long end1 = System.currentTimeMillis();
        System.out.println("time1:" + (end1 - start));
        //远端数据映射
        Collection<Map<String, Object>> data = AssemblyTypeUtil.associatedDataMapping(tableName, dataMapListReturn, null).getData();
        Vector ReturnData = new Vector<>();
        Map<Long, Map<String, Object>> mapData = new LinkedHashMap<>();
        for (Map<String, Object> item : data) {
            Long id = getIndex(item);
            mapData.put(id, item);
        }
        Map<Long, Map<String, Object>> mapDataStore = new LinkedHashMap<>();
        for (Map<String, Object> item : dataMapList2) {
            Long id = getIndex(item);
            mapDataStore.put(id, item);
        }
        List<Long> idSet = Lists.newArrayList(mapData.keySet());
        if(ObjectUtil.isEmpty(sysFromSeachData.getFieldName()) && ObjectUtil.isNotEmpty(sysFromSeachData.getOrder()) && sysFromSeachData.getOrder().equals("desc")){
            idSet = idSet.stream().sorted(Comparator.reverseOrder()).collect(Collectors.toList());
        }
        for (Long id : idSet) {
            mapDataStore.get(id).put("childTableStore",mapData.get(id).get("childTableStore"));
            mapData.get(id).put("bpmStore",mapDataStore.get(id));
            ReturnData.add(mapData.get(id));
        }
        page.setTotal(total.size());
        page.setRecords(ReturnData);
        long end2 = System.currentTimeMillis();
        System.out.println("time2:" + (end2 - start));
        log.info("列表查询数据返回:{}", page.getRecords());
        return  CommonResult.success("获取成功",page);
    }

    private Long getIndex(Map<String, Object> item) {
        return Long.parseLong(String.valueOf(item.get("id")));
    }
    /**
     * 查询条件,按照主表和子表单分组
     */
    private void groupSearchColumn(SysFromSeachDataVo sysFromSeachDataVo, Map<Object, Object> childSearchData, SysFromSeachDataVo sysFromSeachData) {
        //分解字段-主表字段
        List<String> list = sysCurrencyService.getColumnName(sysFromSeachDataVo.getTableName());
        BeanUtils.copyProperties(sysFromSeachDataVo,sysFromSeachData);
        Map<String,Object> mapdata = new HashMap<>();
        Map<Object,Object> mapdatatype = new HashMap<>();
        if(ObjectUtil.isNotEmpty(sysFromSeachData.getSeachData())){
            Set<String> set = sysFromSeachDataVo.getSeachData().keySet();
            for (String o : set) {
                if(list.contains(o)){
                    Map<Object,Object> mapinput = new HashMap<>();
                    mapinput.put(o,sysFromSeachDataVo.getSeachData().get(o));
                    //mapdata.putAll(InputAssemblyTypeUtil.associatedDataMapping(sysFromSeachDataVo.getTableName(),mapinput));
                    mapdata.put(o,sysFromSeachDataVo.getSeachData().get(o));
                    if(ObjectUtil.isEmpty(sysFromSeachData.getSeachData())){
                        mapdatatype.put(o,"like");
                    }else{
                        mapdatatype.put(o,sysFromSeachDataVo.getSearchType().get(o));
                    }
                }else{
                    childSearchData.put(o,sysFromSeachDataVo.getSeachData().get(o));
                    if(ObjectUtil.isEmpty(sysFromSeachData.getSeachData())){
                        childSearchData.put(o,"like");
                    }else{
                        childSearchData.put(o,sysFromSeachDataVo.getSearchType().get(o));
                    }
                }
                sysFromSeachData.setSeachData(mapdata);
                sysFromSeachData.setSearchType(mapdatatype);
            }
        }
    }
    @Override
    public CommonResult selectAllData(String tableName,String form) {
        String sql = "SELECT * FROM "+ tableName + " ORDER BY create_time DESC,id DESC;";
        //ArrayList data = (ArrayList) sysCurrencyMapper.selectData(sql);
        //远端数据映射
        List<Map<String,Object>> dataMapList = sysCurrencyMapper.selectData(sql);
        //子表单插入
        SysFormDO sysForm = sysFormService.selectOneByTableName(tableName);
        List<Map<String, Object>> data;
        if(ObjectUtil.isNotEmpty(sysForm)){
            List<SysFormDO> sysFormList = sysFormService.getChildSysFormByIds(sysForm.getId());
            if(ObjectUtil.isNotEmpty(sysFormList)){
                for (int i = 0; i <sysFormList.size() ; i++) {
                    for (int j = 0; j <dataMapList.size() ; j++) {
                        Long id  = (Long) dataMapList.get(j).get("id");
                        String sqlChild = "SELECT  * from " + sysFormList.get(i).getTableName() + " WHERE parent_id = "+id;
                        List<Map<String,Object>> childDataMapList = sysCurrencyMapper.selectData(sqlChild);
                        dataMapList.get(j).put(sysFormList.get(i).getTableName(), JSON.toJSONString(childDataMapList));
                        dataMapList.get(j).put("tableName", sysFormList.get(i).getTableName());
                    }
                }
            }
            data = AssemblyTypeUtil.associatedDataMapping(tableName, dataMapList, null).getData();
        }else{
            data= AssemblyTypeUtil.associatedDataMapping(tableName,dataMapList,null).getData();
        }
        if(ObjectUtil.isNotEmpty(form)){
            return CommonResult.success("获取成功",data);
        }
        Page<Map<String, Object>> page = new Page<>();
        page.setRecords(data);
        return  CommonResult.success("获取成功",page);
    }


    @Override
    public CommonResult selectAllDataForm(String tableName,String form) {
        String sql = "SELECT * FROM "+ tableName + " ORDER BY create_time DESC,id DESC;";
        //ArrayList data = (ArrayList) sysCurrencyMapper.selectData(sql);
        //远端数据映射
        List<Map<String,Object>> dataMapList = sysCurrencyMapper.selectData(sql);
        //子表单插入
        SysFormDO sysForm = sysFormService.selectOneByTableName(tableName);
        List<SysFormDO> sysFormList = sysFormService.getChildSysFormByIds(sysForm.getId()); //字表
        if (ObjectUtil.isNotEmpty(sysFormList)) {
            dataMapList = dataMapList.parallelStream().map(item -> {
                Set<Map<String,Object>> childTables = new HashSet<>();
                Set<Map<String,Object>> childTablesStore = new HashSet<>();
                for (SysFormDO formDO : sysFormList) {
                    Long id = (Long) item.get("data_id");
                    String sqlChild = "SELECT  * from " + formDO.getTableName() + " WHERE parent_id = " + id + " and version_id = (select max(version_id) from " + formDO.getTableName() + " where parent_id = " + id + ")";
                    List<Map<String, Object>> childDataMapList = sysCurrencyMapper.selectData(sqlChild);
                    List<Map<String, Object>> childDataMapList2 = sysCurrencyMapper.selectData(sqlChild);
                    List<Map<String, Object>> data = AssemblyTypeUtil.associatedDataMapping(formDO.getTableName(), childDataMapList, null).getData();
                    Map<String,Object> childTable = new HashMap<>();
                    childTable.put("tableName", formDO.getTableName());
                    childTable.put("data", JSON.toJSONString(data));
                    childTables.add(childTable);
                    Map<String,Object> childTableStore = new HashMap<>();
                    childTableStore.put("tableName", formDO.getTableName());
                    childTableStore.put("data", childDataMapList2);
                    childTablesStore.add(childTableStore);
                }
                item.put("childTables", childTables);
                item.put("childTableStore", childTablesStore);
                return item;
            }).collect(Collectors.toList());
        }
        //远端数据映射
        Collection<Map<String, Object>> data = AssemblyTypeUtil.associatedDataMapping(tableName, dataMapList, null).getData();
        Vector returnData = new Vector<>();
        Map<Long, Map<String, Object>> mapData = new HashMap<>();
        for (Map<String, Object> item : data) {
            Long id = getIndex(item);
            mapData.put(id, item);
        }
        List<Long> idSet = Lists.newArrayList(mapData.keySet());
        idSet = idSet.stream().sorted(Comparator.reverseOrder()).collect(Collectors.toList());
        for (Long id : idSet) {
            returnData.add(mapData.get(id));
        }
        Page<Vector> page = new Page(1,10);
        page.setRecords(returnData);
        return  CommonResult.success("获取成功",page);
    }
    //搜索集成子表单
    public List<String> selectDataAndChild(SysFromSeachDataVo sysFromSeachData,List<String> idList) {
        //子表单插入
        Set set = sysFromSeachData.getSeachData().keySet();
        String tableName = sysFromSeachData.getTableName();
        SysFormDO sysForm = sysFormService.selectOneByTableName(tableName);
        List<SysFormDO> sysFormList = sysFormService.getChildSysFormByIds(sysForm.getId());
        List<SysFromSeachDataVo> voList = new ArrayList<>();
        if(ObjectUtil.isNotEmpty(sysFormList)){
            for (int i = 0; i <sysFormList.size() ; i++) {
                List<String> listColum = sysCurrencyService.getColumnName(sysFormList.get(i).getTableName());
                SysFromSeachDataVo vo = new SysFromSeachDataVo();
                vo.setTableName(sysFormList.get(i).getTableName());
                Map<String,Object> map = new HashMap<>();
                Map<Object,Object> maptype = new HashMap<>();
                for (Object o : set) {
                    if(listColum.contains(String.valueOf(o))){
                        map.put(String.valueOf(o),String.valueOf(sysFromSeachData.getSeachData().get(o)));
                        maptype.put(String.valueOf(o),String.valueOf(sysFromSeachData.getSearchType().get(o)));
                        vo.setSeachData(map);
                        vo.setSearchType(maptype);
                    }
                }
                voList.add(vo);
            }
        }else{
            return idList;
        }
        List<String> dang = new ArrayList<>();
        if(ObjectUtil.isNotEmpty(voList)){
            for (int i = 0; i <voList.size() ; i++) {
                List<String> result = new ArrayList<>();
                String sqll = "SELECT parent_id from " + sysFormList.get(i).getTableName() + " WHERE 1 = 1 " ;
                //字段搜索拼接
                sqll = SqlUtil.jsonSeachData(voList.get(i),sqll) + " group by parent_id";
                List<String> ids = sysCurrencyMapper.selectDataAndChild(sqll);
                if(i == 0){
                    dang.addAll(ids);
                }
                for (String id : ids) {
                    if (dang.contains(id)) {
                        result.add(id);
                    }
                }
                dang = result;
            }
            List<String> all = new ArrayList<>();
            for (String map : idList) {
                if (dang.contains(map)) {
                    all.add(String.valueOf(map));
                }
            }
            return  all;
        }
        return  idList;
    }

    /**
     * collect 收集时可以排序成原数组数据
     */

    //搜索集成子表单
    public List<SysFromSeachDataVo> selectChildSeachCloum(SysFromSeachDataVo sysFromSeachData) {
        //子表单插入
        Set<String> set = sysFromSeachData.getSeachData().keySet();
        String tableName = sysFromSeachData.getTableName();
        SysFormDO sysForm = sysFormService.selectOneByTableName(tableName);
        List<SysFormDO> sysFormList = sysFormService.getChildSysFormByIds(sysForm.getId());
        List<SysFromSeachDataVo> voList = new ArrayList<>();
        if(ObjectUtil.isNotEmpty(sysFormList)){
            for (int i = 0; i <sysFormList.size() ; i++) {
                List<String> listColum = sysCurrencyService.getColumnName(sysFormList.get(i).getTableName());
                SysFromSeachDataVo vo = new SysFromSeachDataVo();
                vo.setTableName(sysFormList.get(i).getTableName());
                Map<String,Object> map = new HashMap<>();
                Map<Object,Object> maptype = new HashMap<>();
                for (String o : set) {
                    if(listColum.contains(o)){
                        map.put(o,sysFromSeachData.getSeachData().get(o));
                        maptype.put(o,sysFromSeachData.getSearchType().get(o));
                    }
                }
                vo.setSeachData(map);
                vo.setSearchType(maptype);
                voList.add(vo);
            }
        }
        return  voList;
    }

    @Override
    public CommonResult getTableListExcludeForm(Long formId,String tableName) {
        String sql = "select table_name from information_schema.tables where table_schema='"+tableSchema+"';";
        List<String> tableNameLIst = sysCurrencyService.getTableNameList(sql);
        SysFormDO sysform = sysFormService.getSysForm(formId);
        if(sysform.getTableName().equals(tableName)){
            tableNameLIst.remove(sysform.getTableName());
            /*List<SysFormDO> sysFormDOList = sysFormService.getChildSysFormByIds(formId);
            if(ObjectUtil.isNotEmpty(sysFormDOList)){
                for (int i = 0; i <sysFormDOList.size() ; i++) {
                    tableNameLIst.remove(sysFormDOList.get(i).getTableName());
                }
            }*/
        }
        return  CommonResult.success("获取成功",tableNameLIst);
    }

    public List<String> dataRepeatCheck(SysFromInsertDataVo sysFromInsertDataVo,SysFromUpDataVo sysFromUpDataVo) {
        List<SysFromDataColumnVo> data = new ArrayList<>();
        List<String> repeatVerifyList = new ArrayList<>();
        String tableName = "";
        //新增
        if(ObjectUtil.isNotEmpty(sysFromInsertDataVo)){
            data = sysFromInsertDataVo.getData();
            repeatVerifyList = sysFromInsertDataVo.getRepeatVerifyList();
            tableName = sysFromInsertDataVo.getTableName();
        }
        //修改
        if(ObjectUtil.isNotEmpty(sysFromUpDataVo)){
            data = sysFromUpDataVo.getData();
            repeatVerifyList = sysFromUpDataVo.getRepeatVerifyList();
            tableName = sysFromUpDataVo.getTableName();
        }
        List<String> fieldName = new ArrayList<>();
        for (SysFromDataColumnVo vo : data) {
            for (String  Repeatvo : repeatVerifyList) {
                if(vo.getName().equals(Repeatvo)){
                    String sql = "SELECT  * FROM " + tableSchema + "." + tableName +" WHERE ";
                    if(vo.getType().equals("bigint") || vo.getType().equals("int")){
                        sql =  sql + vo.getName() +" = "+ vo.getValue();
                    }else if(vo.getType().equals("datetime") && ObjectUtil.isNotEmpty(vo.getValue())){
                        String valu = SqlUtil.intervalDate(vo.getValue());
                        sql =  sql + vo.getName() +" = '"+ valu + "'";
                    }else{
                        sql = sql + vo.getName() +" = '"+ vo.getValue() + "'";
                    }
                    if(ObjectUtil.isNotEmpty(sysFromUpDataVo)){
                        sql = sql + " and id != '"+ sysFromUpDataVo.getDataId()+"'";
                    }
                    List<Map<Object, Object>> map = sysCurrencyMapper.dataRepeatCheck(sql);
                    if(ObjectUtil.isNotEmpty(map)){
                        fieldName.add(vo.getName());
                    }
                }
            }
        }
        return fieldName;
    }

    @Override
    public CommonResult alterTableSt(SysFromTableStructureVo sysFromTableStructureVo) {
        if(!sysFromTableStructureVo.getTableNameOld().equals(sysFromTableStructureVo.getTableNameNew())){
            String sql = "RENAME TABLE " + sysFromTableStructureVo.getTableNameOld() + " TO "+ sysFromTableStructureVo.getTableNameNew() +"";
            sysCurrencyMapper.alterTableName(sql);
        }
        if(ObjectUtil.isNotEmpty(sysFromTableStructureVo.getData())){
            for (SysFromTableColumnVo structureVo:sysFromTableStructureVo.getData()) {
                String sqls = "";
                if(ObjectUtil.isNotEmpty(structureVo.getNewName())){
                    sqls = "ALTER TABLE " + sysFromTableStructureVo.getTableNameNew()+ " CHANGE ";
                    if("longtext".equals(structureVo.getFormat())){
                        sqls = sqls + "`" + structureVo.getName() + "` `" +structureVo.getNewName()
                                +"` "+structureVo.getFormat() + " COMMENT "
                                + "'" +structureVo.getDesc() + "'";
                    }else{
                        sqls = sqls + "`" + structureVo.getName() + "` `" +structureVo.getNewName()
                                +"` "+structureVo.getFormat() +"(" + structureVo.getLength() + ") COMMENT "
                                + "'" +structureVo.getDesc() + "'";
                    }
                }
                else{
                    sqls = "ALTER TABLE " + sysFromTableStructureVo.getTableNameNew()+ " ADD ";
                    if("longtext".equals(structureVo.getFormat())){
                        sqls = sqls + "`" +structureVo.getNewName()
                                +"` "+structureVo.getFormat()  + " COMMENT "
                                + "'" +structureVo.getDesc() + "'";
                    }else{
                        sqls = sqls + "`" +structureVo.getNewName()
                                +"` "+structureVo.getFormat() +"(" + structureVo.getLength() + ") COMMENT "
                                + "'" +structureVo.getDesc() + "'";
                    }
                }
                sysCurrencyMapper.alterTableSt(sqls);
            }
        }
        return CommonResult.success("修改表结构成功",true);
    }

    @Override
    public CommonResult selectIdsData(String tableName, String ids) {
        String sql = "SELECT * FROM "+ tableName + " WHERE id in ( " + ids + " ) ORDER BY create_time DESC,id DESC;";
        //远端数据映射
        List<Map<String,Object>> dataMapList = sysCurrencyMapper.selectData(sql);
        List<Map<String,Object>> data= AssemblyTypeUtil.associatedDataMapping(tableName,dataMapList,null).getData();
        return  CommonResult.success("获取成功",data);
    }

    @Override
    public List<Map<String,Object>> selectChildData(String tableName, Long id,int versionId) {
        String sql = "SELECT * FROM "+ tableName + " WHERE parent_id = '"+ id + "' and version_id = '" + versionId + "'ORDER BY create_time DESC";
        List<Map<String,Object>> dataa = sysCurrencyMapper.selectData(sql);
        List<Map<String, Object>> data= AssemblyTypeUtil.associatedDataMapping(tableName,dataa,null).getData();
        return  data;
    }


    @Override
    public List<String> getTableName() {
        String sql = "SELECT table_name FROM information_schema.tables WHERE table_schema= '" + tableSchema + "'";
        return sysCurrencyMapper.getTableName(sql);
    }

    @Override
    public Set<String> getData(SysDatasourceVO sysDatasourceVO) {
      /*  String sqlList = "select table_name from information_schema.tables where table_schema='"+tableSchema+"';";
        List<String> tableNameList = sysCurrencyService.getTableNameList(sqlList);
        if(ObjectUtil.isNotEmpty(sysDatasourceVO.getColumn()) && ObjectUtil.isNotEmpty(sysDatasourceVO.getTableName())){
            if(tableNameList.contains(sysDatasourceVO.getTableName())){
                String dataTypeColumnSql = "SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS  WHERE   table_schema= '" + tableSchema + "' AND    COLUMN_NAME = '"  + sysDatasourceVO.getColumn() +  "' AND table_name ='"+sysDatasourceVO.getTableName()+"'  AND data_type LIKE CONCAT('%','datetime','%');";
                String dataTypeColumn = sysCurrencyMapper.getFieldDataByDashboard(dataTypeColumnSql);
                String sql = "";
                if( StringUtils.isNoneBlank(dataTypeColumn)){
                    sql = "SELECT SUBSTRING(  " + sysDatasourceVO.getColumn() + " ,1,19 ) FROM " + tableSchema + "." + sysDatasourceVO.getTableName();
                }else{
                    sql = "SELECT " + sysDatasourceVO.getColumn() + "  FROM " + tableSchema + "." + sysDatasourceVO.getTableName();
                }
                //映射数据
                List<Map<String,Object>> dataMapList = sysCurrencyMapper.getDataSelect(sql);
                List<Map<String,Object>> returnMapList = (List<Map<String, Object>>) AssemblyTypeUtil.associatedDataMapping(sysDatasourceVO.getTableName(),dataMapList,null).getData();
                Set<String> dataList = new HashSet<>();
                for (int i = 0; i <returnMapList.size() ; i++) {
                    Map<String,Object> one = returnMapList.get(i);
                    String data = String.valueOf(one.get(sysDatasourceVO.getColumn()));
                    dataList.add(data);
                }
                return dataList;
            }
        }*/
        CommonResult json = rdbApiService.sourceTestList(Long.parseLong(sysDatasourceVO.getTableName()));
        Map<Object,Object> mappp = (Map<Object, Object>) json.getData();
        List<Map<String,Object>>  mapList = (List<Map<String, Object>>) mappp.get("rowMaps");
        Set<String> dataList = new HashSet<>();
        for (int i = 0; i <mapList.size() ; i++) {
            Map<String,Object> one = mapList.get(i);
            String data = String.valueOf(one.get(sysDatasourceVO.getColumn()));
            dataList.add(data);
        }
        return dataList;
    }

    @Override
    public CommonResult getDataSelect(SysDatasourceVO sysDatasourceVO) {
        if(ObjectUtil.isEmpty(sysDatasourceVO.getSaveColumn())){
            sysDatasourceVO.setSaveColumn("id");
        }
        if(ObjectUtil.isEmpty(sysDatasourceVO.getColumn())){
            return CommonResult.error(500,"无显示字段");
        }
        List<SysSelectDataVO> sysSelectDataList = new ArrayList<>();
        CommonResult json = rdbApiService.sourceTestList(Long.parseLong(sysDatasourceVO.getTableName()));
        Map<Object,Object> mappp = (Map<Object, Object>) json.getData();
        List<Map<String,Object>>  mapList = (List<Map<String, Object>>) mappp.get("rowMaps");
        for (int i = 0; i <mapList.size() ; i++) {
            Map<String,Object> one = mapList.get(i);
            SysSelectDataVO sysSelectDataVO = new SysSelectDataVO();
            sysSelectDataVO.setLabel(String.valueOf(one.get(sysDatasourceVO.getColumn())));
            sysSelectDataVO.setValue(String.valueOf(one.get(sysDatasourceVO.getSaveColumn())));
            sysSelectDataList.add(sysSelectDataVO);
        }
        return CommonResult.success("获取成功",sysSelectDataList);
    }

    @Override
    public CommonResult getColumnFormula(SysFromColumnFormulaVo sysFromColumnFormulaVo) {
        if(ObjectUtil.isEmpty(sysFromColumnFormulaVo.getTableName())){
            return CommonResult.error(500,"表名不能为空");
        }
        if(ObjectUtil.isEmpty(sysFromColumnFormulaVo.getColumnName())){
            return CommonResult.error(500,"字段名不能为空");
        }
        if(ObjectUtil.isEmpty(sysFromColumnFormulaVo.getFormula())){
            return CommonResult.error(500,"计算方式不能为空");
        }
        String formula = "";
        if("sum".equals(sysFromColumnFormulaVo.getFormula())){
            formula = "SUM";
        }
        if("max".equals(sysFromColumnFormulaVo.getFormula())){
            formula = "MAX";
        }
        if("min".equals(sysFromColumnFormulaVo.getFormula())){
            formula = "MIN";
        }
        if("avg".equals(sysFromColumnFormulaVo.getFormula())){
            formula = "AVG";
            String sql = "SELECT FORMAT("+ formula +"("+ sysFromColumnFormulaVo.getColumnName() +"),2) FROM (" + sysFromColumnFormulaVo.getTableName() + ")  INNER JOIN  " + tableMaster + ".system_users sm ON " + sysFromColumnFormulaVo.getTableName() + ".`creator`= sm.id";
            return CommonResult.success(sysCurrencyMapper.getColumnFormula(sql));
        }else{
            String sql = "SELECT "+ formula +"("+ sysFromColumnFormulaVo.getColumnName() +") FROM (" + sysFromColumnFormulaVo.getTableName() + ")  INNER JOIN  " + tableMaster + ".system_users sm ON " + sysFromColumnFormulaVo.getTableName() + ".`creator`= sm.id";
            return CommonResult.success(sysCurrencyMapper.getColumnFormula(sql));
        }
    }

    @Override
    public CommonResult getGatewayColumnFormula(SysFromColumnFormulaVo sysFromColumnFormulaVo) {
        SourceDTO sourceDTO = rdbApiService.getSourceDOById(Long.valueOf(sysFromColumnFormulaVo.getTableName())).getCheckedData();
        if(ObjectUtil.isEmpty(sysFromColumnFormulaVo.getTableName())){
            return CommonResult.error(500,"表名不能为空");
        }
        if(ObjectUtil.isEmpty(sysFromColumnFormulaVo.getColumnName())){
            return CommonResult.error(500,"字段名不能为空");
        }
        if(ObjectUtil.isEmpty(sysFromColumnFormulaVo.getFormula())){
            return CommonResult.error(500,"计算方式不能为空");
        }
        String formula = "";
        if("SUM".equals(sysFromColumnFormulaVo.getFormula())){
            formula = "SUM";
        }
        if("MAX".equals(sysFromColumnFormulaVo.getFormula())){
            formula = "MAX";
        }
        if("MIN".equals(sysFromColumnFormulaVo.getFormula())){
            formula = "MIN";
        }
        if("AVG".equals(sysFromColumnFormulaVo.getFormula())){
            formula = "AVG";
            String sql = "SELECT FORMAT("+ formula +"(sp."+ sysFromColumnFormulaVo.getColumnName() +"),2) FROM (" + sourceDTO.getSqlSentence() + ") sp INNER JOIN  " + tableMaster + ".system_users sm ON sp.`creator`= sm.id";
            return CommonResult.success(sysCurrencyMapper.getColumnFormula(sql));
        }else{
            formula = "DISTINCT";
            String sql = "SELECT SUM("+ formula +"(sp."+ sysFromColumnFormulaVo.getColumnName() +")) FROM (" + sourceDTO.getSqlSentence() + ") sp INNER JOIN  " + tableMaster + ".system_users sm ON sp.`creator`= sm.id";
            return CommonResult.success(sysCurrencyMapper.getColumnFormula(sql));
        }
    }

    @Override
    public String getMax(String tableName,String column) {
        Long tenantId = SecurityFrameworkUtils.getLoginUser().getTenantId();
        String sql = "SELECT " + column + " FROM " + tableName + " WHERE tenant_id = " + tenantId + " ORDER BY id DESC LIMIT 0,1 " ;
        return sysCurrencyMapper.getMaxColumn(sql);
    }

    /**
     * 根据表名、租户、字段名获取
     * @param tableName   表名
     * @param column   编码字段
     * @param match   数据关联数据模糊匹配
     * @return
     */
    public String getPrivateMax(String tableName,String column,String match,Integer start,Integer len) {
        Long tenantId = SecurityFrameworkUtils.getLoginUser().getTenantId();
        String sql = "SELECT " + column +",mid("+ column +","+ start +","+ len +"),COUNT(1) as loopNumber FROM " + tableName + " WHERE tenant_id = " +
                tenantId  + match + " GROUP BY "+ column +",mid("+ column +","+ start +","+ len +") ORDER BY COUNT(1) desc,mid("+ column +","+ start +","+ len +") desc limit 1";
        //String sql = "SELECT " + column + " FROM " + tableName + " WHERE tenant_id = " + tenantId  + match + " ORDER BY id DESC LIMIT 0,1 ";
        return sysCurrencyMapper.getMaxColumn(sql);
    }

    public String getSqlMax(TreeMap<String, String> monitorMap,List<EncodeDetailsDTO> list) {
        String sql = "";
        if(list.size() > 0){
            for (int i = 0; i < list.size(); i++) {
                EncodeDetailsDTO encodeDetailsDO = list.get(i);
                if( monitorMap!=null ){
                    Set<String> keys = monitorMap.keySet();
                    for (String key : keys) {
                        if (key.equals(encodeDetailsDO.getId().toString())) {
                            String monitorData = monitorMap.get(key);
                            if(StringUtils.isNotEmpty(monitorData)){
                                sql = " and " + encodeDetailsDO.getApplyField() + " = " + "'" + monitorData + "'";
                            }
                        }
                    }
                }
            }
        }
        return sql;
    }

    @Override
    public CommonResult getEncode(String tableName, String column, TreeMap<String, String> monitorMap, Long encodeId) {
        int dateIndex = 1;
        int dateFourIndex = 0;
        AtomicReference<String> result = new AtomicReference<>("");
        try{
            if(ObjectUtil.isEmpty(encodeId)){
                return CommonResult.success("参数encodeId 不能为空",null);
            }
            List<EncodeDetailsDTO> list = enCodeDataApi.getDictEncodeDetailsList(encodeId);
            List<EncodeDetailsDTO> filterList = list.stream().filter(item -> item.getPrefixion().equals("4")).collect(Collectors.toList());
//            String sql = getSqlMax(monitorMap,filterList);
//            String value = "";
//            if(StringUtils.isNotEmpty(sql)){
//                value = getPrivateMax(tableName,column,sql);
//            }
            for (int i = 0; i < list.size(); i++) {
                EncodeDetailsDTO encodeDetailsDO = list.get(i);
                switch (encodeDetailsDO.getPrefixion()){
                    case "1":
                        result.set(result + encodeDetailsDO.getLayout());
                        break;
                    case "2":
                        result.set(result + getDateLayout(encodeDetailsDO.getLayout()));
                        break;
                    case "3":
                        if(encodeDetailsDO.getInitialValue() == null){
                            encodeDetailsDO.setInitialValue(0);
                        }
                        if(StringUtils.isBlank( tableName) || StringUtils.isBlank(column)){
                            result.set(result + "~A%"+dateIndex + getSerialLayoutFirst(encodeDetailsDO) + "~A%"+dateIndex);
                        }else{
                            String sql = getSqlMax(monitorMap,filterList);
                            String value = "";
                            if(StringUtils.isNotEmpty(sql)){
                                Integer start = result.get().length() + 5;
                                value = getPrivateMax(tableName,column,sql,start,encodeDetailsDO.getLayout().length());
                            }
                            if(StringUtils.isEmpty(value) && filterList.size() == 0){
                                value = getMax(tableName,column);
                            }
                            String old ="";
                            if(StringUtils.isNoneBlank(value)){
                                List<String> dates = Arrays.asList(value.split("~A%" + dateIndex));
                                old = dates!=null && dates.size()>1 ? dates.get(1) : "";
                            }
                            if(StringUtils.isBlank(value) || old.length() != encodeDetailsDO.getLayout().toString().length()){
                                result.set(result + "~A%"+dateIndex + getSerialLayoutFirst(encodeDetailsDO) + "~A%"+dateIndex);
                            }else{

                                result.set(result + "~A%"+dateIndex + getSerialLayout(encodeDetailsDO,old,value) + "~A%"+dateIndex);
                            }
                        }
                        dateIndex ++;
                        break;
                    case "4":
                        if( monitorMap!=null ){
                            Set<String> keys = monitorMap.keySet();
                            for (String key : keys) {
                                if(key.equals(encodeDetailsDO.getId().toString())){
                                    String monitorData = monitorMap.get(key);

                                    if(StringUtils.isNoneBlank(tableName) && StringUtils.isNoneBlank(column)  && StringUtils.isNoneBlank(encodeDetailsDO.getApplyField()) &&
                                            StringUtils.isNoneBlank(monitorData)){
                                        String showData=null;
                                        CommonResult json = rdbApiService.sourceTestList(Long.parseLong(encodeDetailsDO.getAssociatedForm()));
                                        Map<Object,Object> mappp = (Map<Object, Object>) json.getData();
                                        List<Map<String,Object>>  mapList = (List<Map<String, Object>>) mappp.get("rowMaps");
                                        for (Map<String, Object> map : mapList) {
                                            Set<String> keySet = map.keySet();
                                            for (String key1 : keySet) {
                                                if(key1.equals(encodeDetailsDO.getAssociatedField()) && map.get(key1).equals(monitorData)){
                                                    showData = map.get(encodeDetailsDO.getShowAssociated()) != null ? map.get(encodeDetailsDO.getShowAssociated()).toString() : "";
                                                }
                                            }
                                            if(StringUtils.isNoneBlank(showData)){
                                                break ;
                                            }
                                        }

                                        if(StringUtils.isNoneBlank(showData)){
                                            result.set(result + showData);
                                        }
                                    }
                                }
                            }

                        }
                        break;
                    default:
                        break;
                }
            }
            String showData = result.get();
            for (int i = 1; i <= 9; i++) {
                showData =  showData.replace("~A%"+i,"");
            }
            Map<Object, Object> map = new HashMap<>();
            map.put("showData",showData);
            map.put("storeData",result.get());
            return CommonResult.success("获取成功",map);
        }catch(Exception e){
            e.printStackTrace();
            return CommonResult.error(500,"");
        }
    }


    public String getDateLayout(String layout){
        Date nowTime = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat(layout);
        return sdf.format(nowTime);
    }

    public String getSerialLayout(EncodeDetailsDTO encodeDetailsDO,String old,String value){
        /** 先在数据库中查是否有数据，有数据则拿到数据中最大值后根据步长增加*/
        String result = new String();
        switch (encodeDetailsDO.getLayout()){
            case "0":
                result = String.format("%01d",Integer.parseInt(old) + encodeDetailsDO.getStepSize());
                if(result.length() > 1 ){
                    result = String.format("%01d",encodeDetailsDO.getInitialValue());
                }
                break;
            case "00":
                result = String.format("%02d",Integer.parseInt(old) + encodeDetailsDO.getStepSize());
                if(result.length() > 2 ){
                    result = String.format("%02d",encodeDetailsDO.getInitialValue());
                }
                break;
            case "000":
                result = String.format("%03d",Integer.parseInt(old) + encodeDetailsDO.getStepSize());
                if(result.length() > 3 ){
                    result = String.format("%03d",encodeDetailsDO.getInitialValue());
                }
                break;
            case "0000":
                result = String.format("%04d",Integer.parseInt(old) + encodeDetailsDO.getStepSize());
                if(result.length() > 4 ){
                    result = String.format("%04d",encodeDetailsDO.getInitialValue());
                }
                break;
            case "00000":
                result = String.format("%05d",Integer.parseInt(old) + encodeDetailsDO.getStepSize());
                if(result.length() > 5 ){
                    result = String.format("%05d",encodeDetailsDO.getInitialValue());
                }
                break;
            case "000000":
                result = String.format("%06d",Integer.parseInt(old) + encodeDetailsDO.getStepSize());
                if(result.length() > 6 ){
                    result = String.format("%06d",encodeDetailsDO.getInitialValue());
                }
                break;
            case "0000000":
                result = String.format("%07d",Integer.parseInt(old) + encodeDetailsDO.getStepSize());
                if(result.length() > 7 ){
                    result = String.format("%07d",encodeDetailsDO.getInitialValue());
                }
                break;
            case "00000000":
                result = String.format("%08d",Integer.parseInt(old) + encodeDetailsDO.getStepSize());
                if(result.length() > 8 ){
                    result = String.format("%08d",encodeDetailsDO.getInitialValue());
                }
                break;
            default:
                break;
        }
        return result;
    }


    public String getSerialLayoutFirst(EncodeDetailsDTO encodeDetailsDO){
        /** 先在数据库中查是否有数据，无数据直接用初始值*/
        String result = new String();
        switch (encodeDetailsDO.getLayout()){
            case "0":
                result = String.format("%01d",encodeDetailsDO.getInitialValue());
                break;
            case "00":
                result = String.format("%02d",encodeDetailsDO.getInitialValue());
                break;
            case "000":
                result = String.format("%03d",encodeDetailsDO.getInitialValue());
                break;
            case "0000":
                result = String.format("%04d",encodeDetailsDO.getInitialValue());
                break;
            case "00000":
                result = String.format("%05d",encodeDetailsDO.getInitialValue());
                break;
            case "000000":
                result = String.format("%06d",encodeDetailsDO.getInitialValue());
                break;
            case "0000000":
                result = String.format("%07d",encodeDetailsDO.getInitialValue());
                break;
            case "00000000":
                result = String.format("%08d",encodeDetailsDO.getInitialValue());
                break;
            default:
                break;
        }
        return result;
    }

    @Override
    public CommonResult createTableFlt(SysFromTableVo sysFromVo) {
        String tableName = sysFromVo.getTableName();
        Map<String,Object> mapFlt = new HashMap<>();
        mapFlt.put("tableName",tableName);
        mapFlt.put("columns",sysFromVo.getData().size());
        String fltName = "createTable.flt";
        FreemarkerUtil flt = new FreemarkerUtil();
        String sql = flt.getTemplate(mapFlt,fltName);
        logger.info("sql:   "+sql);
        sysCurrencyMapper.createData(sql);
        return CommonResult.success( "创建成功",true);
    }

    @Override
    @TenantIgnore
    public List<SysTableColumn> getColumn(String tableName) {
        String sql = "SELECT column_name,data_type,column_comment,is_nullable FROM information_schema.columns WHERE table_schema= '" + tableSchema + "' AND table_name = '" + tableName + "'";
        return sysCurrencyMapper.getColumn(sql);
    }

    @Override
    @TenantIgnore
    public List<String> getColumnName(String tableName) {
        String sql = "SELECT column_name FROM information_schema.columns WHERE table_schema= '" + tableSchema + "' AND table_name = '" + tableName + "'";
        return sysCurrencyMapper.getColumnName(sql);
    }

    public CommonResult createFromExcel(MultipartFile file) throws Exception {
        String type = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."));
        InputStream in = file.getInputStream();
        Workbook workbook ;
        //判断后缀 决定使用的解析方式，决定如何创建具体的对象
        if(".xls".equals(type)) {
            //2003版的解析方式
            workbook=new HSSFWorkbook(in);
        }else if(".xlsx".equals(type)) {
            //2007
            workbook=new XSSFWorkbook(in);
        }else {
            //未知内容
            return CommonResult.success("文件格式不正确");
        }
        List<SysExcelTable> tableLIst = new ArrayList<>();
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {//获取每个Sheet表
            Sheet sheet=workbook.getSheetAt(i);
            List<SysRowTable> sysRowTableList = new ArrayList<>();
            int rowNumber = sheet.getPhysicalNumberOfRows();
            if(rowNumber > 1000){
                return CommonResult.error(500,"数据已超过10000行");
            }
            for (int j = 0; j < sheet.getPhysicalNumberOfRows(); j++) {//获取每行
                Row row=sheet.getRow(j);
                List<String> values = new ArrayList<>();
                if(ObjectUtil.isNotEmpty(row)){
                    int cellNmber = row.getPhysicalNumberOfCells();
                    if(cellNmber > 200){
                        return CommonResult.error(500,"数据已超过200列");
                    }
                    for (int k = 0; k < row.getPhysicalNumberOfCells(); k++) {//获取每个单元格
                        System.out.print(row.getCell(k)+"\t");
                        if(ObjectUtil.isNotEmpty(row.getCell(k))){
                            values.add(row.getCell(k).toString());
                        }
                    }
                    SysRowTable sysRowTable = new SysRowTable();
                    if(ObjectUtil.isNotEmpty(values.get(0))){
                        sysRowTable.setRowvalue(values);
                        sysRowTableList.add(sysRowTable);
                    }
                }
            }
            SysExcelTable sysExcelTable = new SysExcelTable();
            sysExcelTable.setRowList(sysRowTableList);
            sysExcelTable.setSheetName(sheet.getSheetName());
            tableLIst.add(sysExcelTable);
        }
        return CommonResult.success(tableLIst);
    }


    @Override
    public CommonResult importData(MultipartFile file, String tableName, String updateType, String parentId) throws Exception {
        //接收id，存储为下一行数据所用。（如果主表数据全部为空，子表数据就关联上一次id），一对多用
        int id = 0;
        int sucessformnumber = 0;
        //接收前一个主表数据是否正确进入，如果失败，excel上关联的子表单数据跳过
        Boolean formtrue = true;
        //是否新增or更新
        if(ObjectUtil.isNotEmpty(updateType) && "update".equals(updateType) && ObjectUtil.isNotEmpty(tableName)){
            String sql = "TRUNCATE TABLE  "+ tableName ;
            sysCurrencyService.deleteDataAll(sql);
        }
        SysExcelReturnVO sysExcelReturnVO = new SysExcelReturnVO();
        //主表form
        SysFormDO sysForm= sysFormService.selectOneByTableName(tableName);
       /* if(ObjectUtil.isEmpty(sysForm)){
            return CommonResult.error(500,"主表单不存在");
        }*/
        //表单字段集合
        List<SysTableColumn> tableColumnList = getColumn(tableName);
        byte [] byteArr=file.getBytes();
        InputStream is = new ByteArrayInputStream(byteArr);
        Workbook sheets = null;
        try {
            String type = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."));
            //判断后缀 决定使用的解析方式，决定如何创建具体的对象
            if (".xls".equals(type)) {
                //2003版的解析方式
                sheets = new HSSFWorkbook(is);
            } else if (".xlsx".equals(type)) {
                //2007
                sheets = new XSSFWorkbook(is);
            } else {
                //未知内容
                return CommonResult.error(500,"文件格式不正确");
            }
        } catch (Exception e) {
            //logger.info(String.valueOf(new HSSFWorkbook(is)));
            return CommonResult.error(500,"excel表头错误，使用excel打开，另存为2003版的excel，再导入");
        }
        // XSSFWorkbook sheets = new XSSFWorkbook(is);
        Sheet  sheetAt = sheets.getSheetAt(0);

        //判断excel大小
        if(sheetAt.getPhysicalNumberOfRows()>platformRowNumber){
            return CommonResult.error(500,"数据行数太多了");
        }
        if(sheetAt.getRow(0).getLastCellNum()>200){
            return CommonResult.error(500,"数据列数太多了");
        }
        //主表信息
        SysExcelInDataVo parentForm = new SysExcelInDataVo();
        parentForm.setTableName(tableName);
        //用作判断是否存在子表单
        List<SysFormDO> sysFormList = sysFormService.getChildSysFormByIds(sysForm.getId());
        //主表表信息
        List<SysInDataVo> dataList = new ArrayList<>();
        //子表信息集合
        List<SysExcelInDataVo> childFormList = new ArrayList<>();
        //导入数据
        List<SysExcelFailedDataVo> sysExcelFailedDataVoListErro = new ArrayList<>();//失败数据
        List<SysExcelFailedDataVo> sysExcelFailedDataVoListSucess = new ArrayList<>();//成功数据
        for (int i = 0; i < sheetAt.getPhysicalNumberOfRows(); i++) {
            Row row = sheetAt.getRow(i);
            //过滤主表字段
            if (i == 0) {
                for (int index = 0; index < row.getPhysicalNumberOfCells(); index++) {
                    Cell cell = row.getCell(index);
                    String cellValue = cell.getStringCellValue();
                    for (int j = 0; j <tableColumnList.size() ; j++) {
                        String columnName = tableColumnList.get(j).getColumnComment();
                        if(ObjectUtil.isNotEmpty(cell)){
                            if(cellValue.equals(columnName) && ObjectUtil.isNotEmpty(cellValue)){
                                SysInDataVo columnVo = new SysInDataVo();
                                columnVo.setCell(index);
                                columnVo.setFildName(tableColumnList.get(j).getColumnName());
                                columnVo.setDataType(tableColumnList.get(j).getDataType());
                                columnVo.setComment(tableColumnList.get(j).getColumnComment());
                                columnVo.setIsNull(tableColumnList.get(j).getIsNullable());
                                dataList.add(columnVo);
                                cellValue = "";
                                tableColumnList.remove(tableColumnList.get(j));
                                //j--;
                            }
                        }
                    }
                }
                parentForm.setDataList(dataList);
                parentForm.setSysFormDO(sysForm);
                if(ObjectUtil.isEmpty(dataList)){
                    return CommonResult.error( 500,"无法找到对应表字段名，请按模板导入");
                }
            }
            int numrow = 0;
            if(ObjectUtil.isNotEmpty(sysFormList)){
                //过滤子表字段
                if (i == 1) {
                    childFormList = excelChildForm(row,sysForm);
                }
                numrow = 1;
            }
            if (i >numrow) {
                //获取excel字段总长
                int columnAll = dataList.size();
                for (int j = 0; j <childFormList.size() ; j++) {
                    columnAll = columnAll + childFormList.get(j).getDataList().size();
                }
                //导入
                //id为上一次主表数据id，保存后传入。为主表合并单元格（一对多所用）
                SysExcelFailedDataVo sysExcelFailedDataVo = excelChildData(row,parentForm,childFormList,columnAll,id,parentId,formtrue);
                //接收id，存储为下一行数据所用。（如果主表数据全部为空，子表数据就关联上一次id），一对多用
                if(id != sysExcelFailedDataVo.getId()){
                    sucessformnumber ++;
                }
                id = sysExcelFailedDataVo.getId();
                //接收前一个主表数据是否正确进入，如果失败，excel上关联的子表单数据跳过
                formtrue = sysExcelFailedDataVo.getFormtrue();
                if(!formtrue){
                    sysExcelFailedDataVoListErro.add(sysExcelFailedDataVo);
                }else{
                    sysExcelFailedDataVoListSucess.add(sysExcelFailedDataVo);
                }
            }
        }
        sysExcelReturnVO.setFailedList(sysExcelFailedDataVoListErro);
        sysExcelReturnVO.setSucessList(sysExcelFailedDataVoListSucess);
        sysExcelReturnVO.setErroNumber(sysExcelFailedDataVoListErro.size());
        //sysExcelReturnVO.setSucesNumver(sysExcelFailedDataVoListSucess.size());
        sysExcelReturnVO.setSucesNumver(sucessformnumber);
        return CommonResult.success( "导入完成!",sysExcelReturnVO);
    }

    //过滤出子表单的字段
    public List<SysExcelInDataVo> excelChildForm(Row row,SysFormDO sysForm) {
        //子表信息集合
        List<SysExcelInDataVo> childFormList = new ArrayList<>();
        //子表form集合
        List<SysFormDO> sysFormList = sysFormService.getChildSysFormByIds(sysForm.getId());
        Set<Integer> set =  new HashSet<>();
        for (SysFormDO sysFormChild: sysFormList) {
            if (ObjectUtil.isNotEmpty(sysFormChild)) {
                SysExcelInDataVo childForm = new SysExcelInDataVo();
                childForm.setTableName(sysFormChild.getTableName());
                List<SysInDataVo> dataList = new ArrayList<>();
                //子表单字段集合
                List<SysTableColumn> childColumnList = getColumn(sysFormChild.getTableName());
                for (int j = 0; j < childColumnList.size(); j++) {
                    String columnName = childColumnList.get(j).getColumnComment();
                    for (int index = 0; index < row.getLastCellNum(); index++) {
                        if(!set.contains(index)){
                            Cell cell = row.getCell(index);
                            String cellValue = cell.getStringCellValue();
                            if (ObjectUtil.isNotEmpty(cell)) {
                                if(cellValue.equals(columnName) && ObjectUtil.isNotEmpty(cellValue)){
                                    SysInDataVo columnVo = new SysInDataVo();
                                    columnVo.setCell(index);
                                    columnVo.setFildName(childColumnList.get(j).getColumnName());
                                    columnVo.setDataType(childColumnList.get(j).getDataType());
                                    columnVo.setComment(childColumnList.get(j).getColumnComment());
                                    columnVo.setIsNull(childColumnList.get(j).getIsNullable());
                                    dataList.add(columnVo);
                                    set.add(index);
                                    cellValue = "";
                                    childColumnList.remove(childColumnList.get(j));
                                }
                            }
                        }
                    }
                }
                childForm.setDataList(dataList);
                childForm.setSysFormDO(sysFormChild);
                childFormList.add(childForm);
            }
        }
        return childFormList;
    }

    public SysExcelFailedDataVo excelChildData(Row row,SysExcelInDataVo parentForm,List<SysExcelInDataVo> childFormList,int columnAll, int id,String parentId,Boolean formtrue) {
        String massageAll = "";
        Boolean alltrue = true;
        Boolean childtrue = true;
        Map<Object,Object> mapData = new HashMap<>();
        for (int index = 0; index < columnAll; index++) {
            if(ObjectUtil.isNotEmpty(row.getCell(index))){
                Cell cell = row.getCell(index);
                //int format = cell.getCellStyle().getDataFormat();
                cell.setCellType(CellType.STRING);
                String value = cell.getRow().getCell(index).toString();
                if(ObjectUtil.isNotEmpty(row) && ObjectUtil.isNotEmpty(cell)){
                    value = cellUtil(cell);
                }
                mapData.put(index,value);
            }else{
                mapData.put(index,"");
            }
        }
        //主表信息插入
        SysFromInsertDataVo sysFromInsertDataVo = new SysFromInsertDataVo();
        sysFromInsertDataVo.setTableName(parentForm.getTableName());
        //字段对象集合
        List<SysFromDataColumnVo> columnList = new ArrayList<>();
        for(Object key:mapData.keySet()){
            for (int i = 0; i <parentForm.getDataList().size() ; i++) {
                if(key.equals(parentForm.getDataList().get(i).getCell())){
                    SysFromDataColumnVo sysFromDataColumnVo = new SysFromDataColumnVo();
                    sysFromDataColumnVo.setName(parentForm.getDataList().get(i).getFildName());
                    sysFromDataColumnVo.setType(parentForm.getDataList().get(i).getDataType());
                    sysFromDataColumnVo.setIsnullable(parentForm.getDataList().get(i).getIsNull());
                    sysFromDataColumnVo.setComment(parentForm.getDataList().get(i).getComment());
                    String value = mapData.get(key).toString();
                    sysFromDataColumnVo.setValue(value);
                    columnList.add(sysFromDataColumnVo);
                }
            }
        }
        SysFromDataColumnVo VoForm = new SysFromDataColumnVo();
        VoForm.setComment("创建者");
        VoForm.setType("bigint");
        VoForm.setName("creator");
        Long userId = SecurityFrameworkUtils.getLoginUser().getId();
        //判断parentId，如果存在则是单独导入子表单数据，parentId为parentId
        if(ObjectUtil.isNotEmpty(parentId)){
            VoForm.setValue(parentId);
        }else{
            //否则为上一次导入的主表数据id为parentId
            VoForm.setValue(String.valueOf(userId));
        }
        columnList.add(VoForm);
       /* Long tenantId = SecurityFrameworkUtils.getLoginUser().getTenantId();
        SysFromDataColumnVo votenantId = new SysFromDataColumnVo();
        votenantId.setComment("租户关联");
        votenantId.setType("int");
        votenantId.setName("tenant_id");
        votenantId.setValue(String.valueOf(tenantId));
        columnList.add(votenantId);*/
        //自动补充版本data_id
        SysFromDataColumnVo VoFormDataId = new SysFromDataColumnVo();
        VoFormDataId.setComment("数据主键id");
        VoFormDataId.setType("bigint");
        VoFormDataId.setName("data_id");
        String data_id = UUIDUtils.dateRandom18();
        VoFormDataId.setValue(data_id);
        columnList.add(VoFormDataId);
        //转换值之前需要保留原始数据
        //原始字段信息集合
        List<SysFromDataColumnVo> failColumnList = new ArrayList<>();
        //原始字段子表单信息集合
        List<SysFromDataColumnVo> failColumnchildList = new ArrayList<>();
        //记录主表字段数据是否全部为空
        Boolean ismerge = false;
        for (int i = 0; i <columnList.size() ; i++) {
            SysFromDataColumnVo vo  = new SysFromDataColumnVo();
            BeanUtils.copyProperties(columnList.get(i),vo);
            failColumnList.add(vo);
            if(ObjectUtil.isNotEmpty(columnList.get(i).getValue())  && !columnList.get(i).getName().equals("creator") && !columnList.get(i).getName().equals("data_id")){
                ismerge = true;
            }
        }
        //判断如果主表字段全部为空，认为已合并单元格获取前一个指标id关联
        if(ismerge){
            SysIfDataVo sysIfData = excelDataRule(columnList,parentForm.getSysFormDO());
            if(sysIfData.getRowistrue()){
                sysFromInsertDataVo.setData(sysIfData.getSysFromDataColumnVoList());
                formtrue= true;
            }else{
                massageAll = massageAll + sysIfData.getMessage();
                alltrue= false;
                //接收前一个主表数据是否正确进入，如果失败，excel上关联的子表单数据跳过
                formtrue= false;
            }
        }
        //子表数据插入
        List<SysFromInsertDataVo> sysChildFromInsertDataVoList = new ArrayList<>();
        //字段对象集合总
        List<SysFromDataColumnVo> childColumnListAll = new ArrayList<>();
        if(ObjectUtil.isNotEmpty(childFormList)){
            for (int i = 0; i <childFormList.size() ; i++) {
                SysExcelInDataVo childForm = childFormList.get(i);
                SysFromInsertDataVo sysChildFromInsertDataVo = new SysFromInsertDataVo();
                sysChildFromInsertDataVo.setTableName(childForm.getTableName());
                //字段对象集合
                List<SysFromDataColumnVo> childColumnList = new ArrayList<>();
                for(Object key:mapData.keySet()){
                    for (int j = 0; j <childForm.getDataList().size() ; j++) {
                        if(key.equals(childForm.getDataList().get(j).getCell())){
                            SysFromDataColumnVo sysFromDataColumnVo = new SysFromDataColumnVo();
                            sysFromDataColumnVo.setName(childForm.getDataList().get(j).getFildName());
                            sysFromDataColumnVo.setType(childForm.getDataList().get(j).getDataType());
                            sysFromDataColumnVo.setIsnullable(childForm.getDataList().get(j).getIsNull());
                            sysFromDataColumnVo.setComment(childForm.getDataList().get(j).getComment());
                            String value = mapData.get(key).toString();
                            sysFromDataColumnVo.setValue(value);
                            childColumnList.add(sysFromDataColumnVo);
                        }
                    }
                }
                childColumnListAll.addAll(childColumnList);
                childColumnList.add(VoForm);
                for (int j = 0; j <childColumnListAll.size() ; j++) {
                    SysFromDataColumnVo vo  = new SysFromDataColumnVo();
                    BeanUtils.copyProperties(childColumnListAll.get(j),vo);
                    failColumnchildList.add(vo);
                }
                SysIfDataVo sysIfDataChild = excelDataRule(childColumnList,childForm.getSysFormDO());
                if(sysIfDataChild.getRowistrue()){
                    //子表单字段注入
                    sysChildFromInsertDataVo.setData(sysIfDataChild.getSysFromDataColumnVoList());
                    //放入子表集合
                    sysChildFromInsertDataVoList.add(sysChildFromInsertDataVo);
                    //标记成功返回
                    SysExcelFailedDataVo vo = new SysExcelFailedDataVo();
                }else{
                    massageAll = massageAll + sysIfDataChild.getMessage();
                    alltrue= false;
                }
            }
        }
        SysExcelFailedDataVo voin = new SysExcelFailedDataVo();
        if(alltrue) {
            //判断如果主表单所有字段为空，认为合并单元格
            if (ismerge) {
                //parent_id为上一次存储id
                excelInsertData(sysFromInsertDataVo).getData();
                id = Integer.parseInt(data_id);
            }
        }
        //接收前一个主表数据是否正确进入，如果失败，excel上关联的子表单数据跳过----formtrue
        if(childtrue && formtrue) {
            for (int i = 0; i <sysChildFromInsertDataVoList.size() ; i++) {
                SysFromInsertDataVo sysFromInsertDataVoChild = sysChildFromInsertDataVoList.get(i);
                SysFromDataColumnVo Void = new SysFromDataColumnVo();
                Void.setComment("主表单关联id");
                Void.setType("bigint");
                Void.setName("parent_id");
                Void.setValue(String.valueOf(id));
                columnList.add(Void);
              /*  SysFromDataColumnVo votenantChildId = new SysFromDataColumnVo();
                votenantChildId.setComment("租户关联");
                votenantChildId.setType("int");
                votenantChildId.setName("tenant_id");
                votenantChildId.setValue(String.valueOf(tenantId));
                columnList.add(votenantChildId);*/
                //自动补充版本data_id
                SysFromDataColumnVo VoFormDataIdChild = new SysFromDataColumnVo();
                VoFormDataIdChild.setComment("数据主键id");
                VoFormDataIdChild.setType("bigint");
                VoFormDataIdChild.setName("data_id");
                VoFormDataIdChild.setValue(UUIDUtils.dateRandom18());
                columnList.add(VoFormDataIdChild);
                List<SysFromDataColumnVo> sysFromDataColumnVoList = sysFromInsertDataVoChild.getData();
                sysFromDataColumnVoList.add(VoFormDataIdChild);
                sysFromDataColumnVoList.add(Void);
                sysFromInsertDataVoChild.setData(sysFromDataColumnVoList);
                excelInsertData(sysFromInsertDataVoChild);
            }
        }
        if(alltrue && childtrue){
            //返回成功信息
            List<SysFromDataColumnVo> columnListAll = new ArrayList<>();
            columnListAll.addAll(columnList);
            columnListAll.addAll(childColumnListAll);
            voin.setData(columnList);
            voin.setRowNumber(row.getRowNum()+1);
            //主表数据id返回
            voin.setId(id);
            voin.setFormtrue(childtrue);
            return voin;
        }
        SysExcelFailedDataVo voinerro = new SysExcelFailedDataVo();
        //返回失败信息
        List<SysFromDataColumnVo> columnListAll = new ArrayList<>();
        columnListAll.addAll(failColumnList);
        columnListAll.addAll(failColumnchildList);
        voinerro.setData(columnListAll);
        voinerro.setRowNumber(row.getRowNum()-1);
        voinerro.setFailure(massageAll);
        voinerro.setId(id);
        voinerro.setFormtrue(alltrue);
        return voinerro;
    }

    public SysIfDataVo excelDataRule(List<SysFromDataColumnVo> sysFromDataColumnVoList,SysFormDO sysFormDO) {
        String errovale = "";
        Boolean rowistrue = true;
        for (int i = 0; i <sysFromDataColumnVoList.size() ; i++) {
            SysFromDataColumnVo sysFromDataColumnVo = sysFromDataColumnVoList.get(i);
            //数据类型int
            //Boolean typeint = TypeUtil.isreturnint(sysFromDataColumnVo);
            //数据类型
            Boolean type = TypeUtil.isreturn(sysFromDataColumnVo,sysFormDO);
            //是否为空
            Boolean isnull = IsNullUtil.isnullForm(sysFromDataColumnVo.getName(),sysFromDataColumnVo.getValue(),sysFormDO);
            //下拉组件、单选、多选
            Map<Object,Object> mapselect = IsNullUtil.selectInForm(sysFromDataColumnVo.getName(),sysFromDataColumnVo.getValue(),sysFormDO);
            //部门组件
            Map<Object,Object> mapdepart = IsNullUtil.departForm(sysFromDataColumnVo.getName(),sysFromDataColumnVo.getValue(),sysFormDO);
            //日期/时间组件
            Map<Object,Object> mapdate = IsNullUtil.DateInForm(sysFromDataColumnVo.getName(),sysFromDataColumnVo.getValue(),sysFormDO);
            //是否重复
            Boolean dataRepeat = IsNullUtil.isDataRepeat(sysFromDataColumnVo.getName(),sysFromDataColumnVo.getValue(),sysFormDO);
            /*if(!typeint){
                errovale = errovale + sysFromDataColumnVo.getComment() + "类型" + sysFromDataColumnVo.getType() + "不匹配;或最大值不能超过2147483647";
                rowistrue = false ;
            }*/
            if(!type){
                errovale = errovale + sysFromDataColumnVo.getComment() + "类型" + sysFromDataColumnVo.getType() + "不匹配;";
                rowistrue = false ;
            }
            if(!isnull){
                errovale = errovale + sysFromDataColumnVo.getComment() + "为必填;";
                rowistrue = false ;
            }
            if(ObjectUtil.isNotEmpty(mapselect)){
                if(!((Boolean)mapselect.get("isselect")).booleanValue()){
                    errovale = errovale + sysFromDataColumnVo.getComment() + "-规则值错误-" + mapselect.get("req");
                    rowistrue = false ;
                }
                sysFromDataColumnVo.setValue(String.valueOf(mapselect.get("value")));
            }
            if(ObjectUtil.isNotEmpty(mapdepart)){
                if(!((Boolean)mapdepart.get("isselect")).booleanValue()){
                    errovale = errovale + sysFromDataColumnVo.getComment() + "-部门全路径错误-" + mapdepart.get("req");
                    rowistrue = false ;
                }
                sysFromDataColumnVo.setValue(String.valueOf(mapdepart.get("value")));
            }
            if(ObjectUtil.isNotEmpty(mapdate)){
                if(!((Boolean)mapdate.get("isselect")).booleanValue()){
                    errovale = errovale + sysFromDataColumnVo.getComment() + "-规则值错误-" + mapdate.get("req");
                    rowistrue = false ;
                }
                sysFromDataColumnVo.setValue(String.valueOf(mapdate.get("value")));
            }
            if(!dataRepeat){
                errovale = errovale + sysFromDataColumnVo.getComment() + "数据重复;";
                rowistrue = false ;
            }
        }
        SysIfDataVo sysIfData = new SysIfDataVo();
        sysIfData.setRowistrue(rowistrue);
        sysIfData.setMessage(errovale);
        sysIfData.setSysFromDataColumnVoList(sysFromDataColumnVoList);
        return sysIfData;
    }
    //}

    public String cellUtil(Cell cell)  {
        if(ObjectUtil.isNotEmpty(cell)){
            String value = "";
            switch(cell.getCellTypeEnum()) {
                case STRING:
                    value = cell.getRichStringCellValue().getString();
                    break;
                case NUMERIC:
                    if(DateUtil.isCellDateFormatted(cell)) {
                        Date d = cell.getDateCellValue();
                        DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        value = formater.format(d);
                    } else {
                        value = String.valueOf(cell.getNumericCellValue());
                    }
                    break;
                case BOOLEAN:
                    value = String.valueOf(cell.getBooleanCellValue());
                    break;
                case FORMULA:
                    value = String.valueOf(cell.getCellFormula());
                    break;
                case BLANK:
                    value = "";
                    break;
            }
            return value;
        }
        return null;
    }
    public String cellTypeUtil(String type)  {
        if(type.equals("number")){ return "int"; }
        if(type.equals("time")){ return "datetime"; }
        if(type.equals("input")){ return "varchar"; }
        return "varchar";
    }


    /**
     * 导入模板下载
     * @param tableName
     * @param response
     * @throws IOException
     */
    @Override
    public void importTemplate(String tableName, HttpServletResponse response) throws Exception {
        List<SysTableColumn> columnList = getColumn(tableName);
        if(columnList.size() > 0){
            /** 表存在*/
            SysFormDO sysFormDO = sysFormService.selectOneByTableName(tableName);
            ByteArrayOutputStream baos =  new ByteArrayOutputStream();
            Workbook workbook = new XSSFWorkbook();
            //创建模板sheet
            XSSFSheet sheet = (XSSFSheet)formTemplate(workbook,sysFormDO,columnList);
            //创建规则sheet
            sheet = (XSSFSheet)ruleTemplate(workbook,sysFormDO,columnList);
            //导出
            response.setContentType("application/octet-stream; charset=UTF-8");
            response.setHeader("Access-Control-Allow-Origin", "*");
            response.setHeader("Access-Control-Expose-Headers", "*");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment" + URLEncoder.encode(sysFormDO.getFormName()+".xlsx","utf-8"));
            response.setCharacterEncoding("utf-8");
            workbook.write(response.getOutputStream());
            baos.close();
        }
    }

    public XSSFSheet formTemplate(Workbook workbook,SysFormDO sysFormDO,List<SysTableColumn> columnList) {
        XSSFSheet sheet = (XSSFSheet)workbook.createSheet(sysFormDO.getFormName());
        XSSFCellStyle cellStyle = (XSSFCellStyle) workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER); // 居中
        cellStyle.setLocked(true);
        sheet.setDefaultColumnWidth(20);
        sheet.setDefaultRowHeightInPoints(20);
        Row row = sheet.createRow(0);
        Integer count = 0 ;
        Integer srartchild = 0 ;
        List<SysFormDO> sysFormChildList = sysFormService.getChildSysFormByIds(sysFormDO.getId());
        if(null != sysFormDO && null != sysFormDO.getFormJsonUser()){
            for(int i = 0;i < columnList.size(); i++){
                if(ObjectUtil.isNotEmpty(columnList.get(i).getColumnComment()) && !"id".equals(columnList.get(i).getColumnName()) && !"create_time".equals(columnList.get(i).getColumnName()) && !"tenant_id".equals(columnList.get(i).getColumnName()) && !"update_time".equals(columnList.get(i).getColumnName()) && !"data_type".equals(columnList.get(i).getColumnName()) && !"creator".equals(columnList.get(i).getColumnName()) &&!"parent_id".equals(columnList.get(i).getColumnName()) &&!"relation_id".equals(columnList.get(i).getColumnName()) &&!"data_id".equals(columnList.get(i).getColumnName()) &&!"version_id".equals(columnList.get(i).getColumnName()) &&!"approve_status".equals(columnList.get(i).getColumnName()) &&!"is_offline".equals(columnList.get(i).getColumnName())){
                    row.createCell(count).setCellValue(columnList.get(i).getColumnComment());
                    row.getCell(count).setCellStyle(cellStyle);
                    if(ObjectUtil.isNotEmpty(sysFormChildList)) {
                        CellRangeAddress rangenew  = new CellRangeAddress(0,1,srartchild,count);
                        sheet.addMergedRegion(rangenew);
                    }
                    count++;
                    srartchild++;
                }
            }
            if(ObjectUtil.isNotEmpty(sysFormChildList)) {
                Row rowe = sheet.createRow(1);
                for(int i = 0;i < sysFormChildList.size(); i++){
                    SysFormDO childSysForm = sysFormChildList.get(i);
                    List<SysTableColumn> childColumnList = getColumn(childSysForm.getTableName());
                    for (int j = 0; j < childColumnList.size(); j++) {
                        if (ObjectUtil.isNotEmpty(columnList.get(i).getColumnComment()) && !"id".equals(childColumnList.get(j).getColumnName()) && !"create_time".equals(childColumnList.get(j).getColumnName()) && !"tenant_id".equals(childColumnList.get(j).getColumnName()) && !"update_time".equals(childColumnList.get(j).getColumnName()) && !"data_type".equals(childColumnList.get(j).getColumnName()) && !"creator".equals(childColumnList.get(j).getColumnName()) && !"parent_id".equals(childColumnList.get(j).getColumnName()) && !"relation_id".equals(childColumnList.get(j).getColumnName()) &&!"data_id".equals(childColumnList.get(j).getColumnName()) &&!"version_id".equals(childColumnList.get(j).getColumnName()) &&!"approve_status".equals(childColumnList.get(j).getColumnName()) &&!"is_offline".equals(childColumnList.get(j).getColumnName())) {
                            //子表单字段
                            rowe.createCell(count).setCellValue(childColumnList.get(j).getColumnComment());
                            rowe.getCell(count).setCellStyle(cellStyle);
                            count++;
                        }
                    }
                    //子表单标题
                    row.createCell(srartchild).setCellValue(childSysForm.getFormName());
                    row.getCell(srartchild).setCellStyle(cellStyle);
                    //合并单元格区域只有一个单元格时，不合并
                    if (srartchild != count-1 ) {
                        CellRangeAddress rangenew = new CellRangeAddress(0, 0, srartchild, count-1);
                        sheet.addMergedRegion(rangenew);
                    }
                    srartchild = count;
                }
            }
        }
        return sheet;
    }

    /**
     * 导出数据
     * @param tableName
     * @param response
     * @throws IOException
     */
    @Override
    public void getDataOut(String tableName, String ids, String sortField, String sortType, HttpServletResponse response) throws Exception {
        List<SysTableColumn> columnList = getColumn(tableName);
        if(columnList.size() > 0){
            /** 表存在*/
            SysFormDO sysFormDO = sysFormService.selectOneByTableName(tableName);
            ByteArrayOutputStream baos =  new ByteArrayOutputStream();
            Workbook workbook = new XSSFWorkbook();
            //创建模板sheet
            XSSFSheet sheet = (XSSFSheet)dataOutExcel(workbook,ids,sortField,sortType,sysFormDO,columnList);
            //导出
            response.setContentType("application/octet-stream; charset=UTF-8");
            response.setHeader("Access-Control-Allow-Origin", "*");
            response.setHeader("Access-Control-Expose-Headers", "*");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment" + URLEncoder.encode(sysFormDO.getFormName()+".xlsx","utf-8"));
            response.setCharacterEncoding("utf-8");
            workbook.write(response.getOutputStream());
            baos.close();
            /** 表存在*//*
            SysFormDO sysFormDO = sysFormService.selectOneByTableName(tableName);
            SysDownload sysDownload = new SysDownload();
            sysDownload.setDownloadState("下载中");
            sysDownload.setTableName(sysFormDO.getFormName());
            sysDownloadMapper.insert(sysDownload);
            Long id = sysDownload.getId();
            Workbook workbook = new XSSFWorkbook();
            //创建模板sheet
            XSSFSheet sheet = (XSSFSheet)dataOutExcel(workbook,ids,sortField,sortType,sysFormDO,columnList);
            //创建字节数组输出流
            ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
            //输出缓冲流
            OutputStream output = new BufferedOutputStream(byteArrayOutputStream);
            //将excel写入到输出流中
            workbook.write(output);
            //刷新缓冲区
            output.flush();
            //把输出流的数据拿到
            byte[] bytes = byteArrayOutputStream.toByteArray();
            // 存储文件
            String avatar = fileApi.createFile(sysFormDO.getFormName() + ".xlsx", null, bytes);
            SysDownload sysDownloadreturn = sysDownloadMapper.selectById(id);
            sysDownloadreturn.setDownloadUrl(avatar);
            sysDownloadreturn.setDownloadState("完成");
            sysDownloadMapper.updateBatch(sysDownloadreturn);*/
        }
    }

    public XSSFSheet dataOutExcel(Workbook workbook,String ids,String sortField, String sortType,SysFormDO sysFormDO,List<SysTableColumn> columnList) {
        XSSFSheet sheet = (XSSFSheet)workbook.createSheet(sysFormDO.getFormName());
        XSSFCellStyle cellStyle = (XSSFCellStyle) workbook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//设置水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//设置垂直居中
        cellStyle.setLocked(true);
        sheet.setDefaultColumnWidth(15);
        sheet.setDefaultRowHeightInPoints(18);
        Row row = sheet.createRow(0);
        Integer count = 0 ;
        Integer srartchild = 0 ;
        List<SysFormDO> sysFormChildList = sysFormService.getChildSysFormByIds(sysFormDO.getId());
        if(null != sysFormDO && null != sysFormDO.getFormJsonUser()){
            for(int i = 0;i < columnList.size(); i++){
                if(!"id".equals(columnList.get(i).getColumnName()) && !"data_id".equals(columnList.get(i).getColumnName()) && !"tenant_id".equals(columnList.get(i).getColumnName()) && !"update_time".equals(columnList.get(i).getColumnName()) && !"data_type".equals(columnList.get(i).getColumnName()) && !"creator".equals(columnList.get(i).getColumnName()) &&!"parent_id".equals(columnList.get(i).getColumnName()) &&!"relation_id".equals(columnList.get(i).getColumnName()) &&!"version_id".equals(columnList.get(i).getColumnName()) &&!"approve_status".equals(columnList.get(i).getColumnName()) &&!"is_offline".equals(columnList.get(i).getColumnName())){
                    row.createCell(count).setCellValue(columnList.get(i).getColumnComment());
                    row.getCell(count).setCellStyle(cellStyle);
                    if(ObjectUtil.isNotEmpty(sysFormChildList)) {
                        CellRangeAddress rangenew  = new CellRangeAddress(0,1,srartchild,count);
                        sheet.addMergedRegion(rangenew);
                    }
                    count++;
                    srartchild++;
                }
            }
            if(ObjectUtil.isNotEmpty(sysFormChildList)) {
                Row rowe = sheet.createRow(1);
                for(int i = 0;i < sysFormChildList.size(); i++){
                    SysFormDO childSysForm = sysFormChildList.get(i);
                    List<SysTableColumn> childColumnList = getColumn(childSysForm.getTableName());
                    for (int j = 0; j < childColumnList.size(); j++) {
                        if (!"id".equals(childColumnList.get(j).getColumnName()) && !"create_time".equals(childColumnList.get(j).getColumnName()) && !"data_id".equals(childColumnList.get(j).getColumnName()) && !"tenant_id".equals(childColumnList.get(j).getColumnName()) && !"update_time".equals(childColumnList.get(j).getColumnName()) && !"data_type".equals(childColumnList.get(j).getColumnName()) && !"creator".equals(childColumnList.get(j).getColumnName()) && !"parent_id".equals(childColumnList.get(j).getColumnName()) && !"relation_id".equals(childColumnList.get(j).getColumnName()) &&!"version_id".equals(childColumnList.get(j).getColumnName()) &&!"approve_status".equals(childColumnList.get(j).getColumnName()) &&!"is_offline".equals(childColumnList.get(j).getColumnName())) {
                            //子表单字段
                            rowe.createCell(count).setCellValue(childColumnList.get(j).getColumnComment());
                            rowe.getCell(count).setCellStyle(cellStyle);
                            count++;
                        }
                    }
                    //子表单标题
                    row.createCell(srartchild).setCellValue(childSysForm.getFormName());
                    row.getCell(srartchild).setCellStyle(cellStyle);
                    //合并单元格区域只有一个单元格时，不合并
                    if (srartchild != count-1 ) {
                        CellRangeAddress rangenew = new CellRangeAddress(0, 0, srartchild, count-1);
                        sheet.addMergedRegion(rangenew);
                    }
                    srartchild = count;
                }
            }
        }
        Integer countdata = 0 ;
        Integer srartchilddata = 0 ;
        Integer datasize = 0 ;
        Integer datasizeAll = 0 ;
        Integer fristsize = 1 ;
        Integer rownumber = countdata+2 ;
        String sqldata = "SELECT * FROM " + sysFormDO.getTableName() + " a INNER JOIN (SELECT MAX(version_id),id FROM " + sysFormDO.getTableName() + " GROUP BY data_id) b WHERE a.id = b.id";
        if(ObjectUtil.isNotEmpty(ids)){
            sqldata = "SELECT * FROM " + sysFormDO.getTableName() + " a WHERE a.id in ( " + ids + " )";
        }else{
            String sql = "SELECT  sp.id  FROM " + tableSchema + "."+ sysFormDO.getTableName() + " sp INNER JOIN " + tableMaster + ".system_users sm  ON sp.`creator`= sm.id  ";
            List<String> idList = adminUserApi.selectData2(sql).getCheckedData();
            //List<String> idList = sysCurrencyMapper.getIds(sql);
            String join = StringUtils.join(idList, ',');
            if(StringUtils.isNoneBlank(join)){
                sqldata = "SELECT * FROM " + sysFormDO.getTableName() + " a INNER JOIN (SELECT MAX(version_id),id FROM " + sysFormDO.getTableName() + " GROUP BY data_id) b WHERE a.id = b.id and a.id in (" + join + ") ";
            }else{
                sqldata = "SELECT * FROM " + sysFormDO.getTableName() + " a INNER JOIN (SELECT MAX(version_id),id FROM " + sysFormDO.getTableName() + " GROUP BY data_id) b WHERE a.id = b.id and a.id in ( 0 ) ";
            }
            //sqldata = "SELECT * FROM " + sysFormDO.getTableName() + " a INNER JOIN (SELECT MAX(version_id),id FROM " + sysFormDO.getTableName() + " GROUP BY data_id) b WHERE a.id = b.id and id in (" + join + ") ";
        }
        if("".equals(sortField) && !"".equals(sortType)){
            sqldata = sqldata + " ORDER BY a.id " + sortType;
        }
        if(!"".equals(sortField) && !"".equals(sortType)){
            sqldata = sqldata + " ORDER BY a." + sortField + " " + sortType;
        }
        List<Map<String,Object>> getDataOutList = sysCurrencyMapper.getDataOut(sqldata);
        getDataOutList = AssemblyTypeUtil.associatedDataMapping(sysFormDO.getTableName(),getDataOutList,"outdata").getData();
        if(sysFormChildList.size() == 0){
            rownumber--;
        }
        //循环主数据
        for (int i = 0; i < getDataOutList.size(); i++) {
            if(sysFormChildList.size() > 0 && i == 0) {
                fristsize = 2;
            }
            //排除表头之后第一条row
            Row rowdata = sheet.createRow(rownumber);
            //第一条主数据
            Map<String,Object> map = getDataOutList.get(i);
            //如果存在子表单
            if(sysFormChildList.size() > 0) {
                //循环字表单
                for(int k = 0;k < sysFormChildList.size(); k++) {
                    SysFormDO childSysForm = sysFormChildList.get(k);
                    String sqldatachild = "SELECT * FROM "+ childSysForm.getTableName() + " WHERE parent_id=" + map.get("data_id");
                    //每个字表单的对应的主数据的子数据
                    List<Map<String,Object>> getDataOutListChild = sysCurrencyMapper.getDataOut(sqldatachild);
                    if(getDataOutListChild.size() > datasize){
                        //保存主数据对应子数据最多的数据个数
                        datasize = getDataOutListChild.size();
                    }
                }
                if(datasize<2){
                    //如果循环完子表单datasize小于2，说明所有子表单没有对应注表单数据或者只有一条，所以row加1即可
                    rownumber ++;
                }
            }else{
                //不存在子表单的话说明就是一条主数据，无需合并,所以增加一条row写下一条数据
                rownumber ++;
            }
            //拿到主数据对应最多的子数据个数后建立主数据第一条数据
            for (int j = 0; j <columnList.size() ; j++) {
                SysTableColumn vo = columnList.get(j);
                if("create_time".equals(vo.getColumnName())){
                    String createTime = String.valueOf(map.get(vo.getColumnName())).replace("T"," ");
                    map.put(vo.getColumnName(),createTime);
                }
                if (!"id".equals(vo.getColumnName()) && !"data_id".equals(vo.getColumnName()) && !"tenant_id".equals(vo.getColumnName()) && !"update_time".equals(vo.getColumnName()) && !"data_type".equals(vo.getColumnName()) && !"creator".equals(vo.getColumnName()) && !"parent_id".equals(vo.getColumnName()) && !"relation_id".equals(vo.getColumnName()) &&!"version_id".equals(vo.getColumnName()) &&!"approve_status".equals(vo.getColumnName()) &&!"is_offline".equals(vo.getColumnName())) {
                    rowdata.createCell(countdata).setCellValue(String.valueOf(map.get(vo.getColumnName())));
                    rowdata.getCell(countdata).setCellStyle(cellStyle);
                    if(datasize>1){
                        datasizeAll = fristsize + datasize -1;
                        CellRangeAddress rangeneww  = new CellRangeAddress(fristsize,datasizeAll,srartchilddata,countdata);
                        sheet.addMergedRegion(rangeneww);
                    }
                    countdata++;
                    srartchilddata++;
                }
            }
            if(ObjectUtil.isNotEmpty(sysFormChildList)) {
                int returncountdata = countdata;
                if(datasize == 0){
                    datasize ++;
                }
                for (int j = 0; j < datasize; j++) {
                    for (int k = 0; k < sysFormChildList.size(); k++) {
                        SysFormDO childSysForm = sysFormChildList.get(k);
                        List<SysTableColumn> childColumnList = getColumn(childSysForm.getTableName());
                        String sqldatachild = "SELECT * FROM " + childSysForm.getTableName() + " WHERE parent_id=" + map.get("data_id");
                        List<Map<String, Object>> getDataOutListChild = sysCurrencyMapper.getDataOut(sqldatachild);
                        getDataOutListChild= AssemblyTypeUtil.associatedDataMapping(childSysForm.getTableName(),getDataOutListChild,"outdata").getData();
                        if(getDataOutListChild.size()>j){
                            Map<String, Object> mapchild = getDataOutListChild.get(j);
                            if (ObjectUtil.isNotEmpty(mapchild)) {
                                for (int z = 0; z < childColumnList.size(); z++) {
                                    SysTableColumn childvo = childColumnList.get(z);
                                    if (!"id".equals(childvo.getColumnName()) && !"create_time".equals(childvo.getColumnName()) && !"data_id".equals(childvo.getColumnName()) && !"tenant_id".equals(childvo.getColumnName()) && !"update_time".equals(childvo.getColumnName()) && !"data_type".equals(childvo.getColumnName()) && !"creator".equals(childvo.getColumnName()) && !"parent_id".equals(childvo.getColumnName()) && !"relation_id".equals(childvo.getColumnName()) &&!"version_id".equals(childvo.getColumnName()) &&!"approve_status".equals(childvo.getColumnName()) &&!"is_offline".equals(childvo.getColumnName())) {
                                        rowdata.createCell(countdata).setCellValue(String.valueOf(mapchild.get(childvo.getColumnName())));
                                        rowdata.getCell(countdata).setCellStyle(cellStyle);
                                        countdata++;
                                    }
                                }
                            }
                        }else{
                            for (int z = 0; z < childColumnList.size(); z++) {
                                SysTableColumn childvo = childColumnList.get(z);
                                if (!"id".equals(childvo.getColumnName()) && !"data_id".equals(childvo.getColumnName()) && !"tenant_id".equals(childvo.getColumnName()) && !"update_time".equals(childvo.getColumnName()) && !"data_type".equals(childvo.getColumnName()) && !"creator".equals(childvo.getColumnName()) && !"parent_id".equals(childvo.getColumnName()) && !"relation_id".equals(childvo.getColumnName()) &&!"version_id".equals(childvo.getColumnName()) &&!"approve_status".equals(childvo.getColumnName()) &&!"is_offline".equals(childvo.getColumnName())) {
                                    rowdata.createCell(countdata).setCellValue("");
                                    countdata++;
                                }
                            }
                        }
                    }
                    if(datasize != 1){
                        rownumber++;
                    }
                    rowdata = sheet.createRow(rownumber);
                    countdata = returncountdata;
                }
            }
            fristsize = fristsize + datasize;
            countdata = 0;
            datasize = 0;
            srartchilddata = 0;
        }
        return sheet;
    }

    public XSSFSheet ruleTemplate(Workbook workbook,SysFormDO sysFormDO,List<SysTableColumn> columnList) {
        XSSFSheet sheet = (XSSFSheet)workbook.createSheet(sysFormDO.getFormName()+"规则");
        XSSFCellStyle cellStyle = (XSSFCellStyle) workbook.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 上下居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//设置水平居中
        sheet.setDefaultColumnWidth(20);
        sheet.setDefaultRowHeightInPoints(20);
        sheet.setColumnWidth(7, 500*20);
        //表头
        Row row = sheet.createRow(0);
        row.createCell(0).setCellValue("表名");
        row.getCell(0).setCellStyle(cellStyle);
        row.createCell(1).setCellValue("字段名");
        row.getCell(1).setCellStyle(cellStyle);
        row.createCell(2).setCellValue("类型");
        row.getCell(2).setCellStyle(cellStyle);
        row.createCell(3).setCellValue("必填");
        row.getCell(3).setCellStyle(cellStyle);
        row.createCell(4).setCellValue("默认值");
        row.getCell(4).setCellStyle(cellStyle);
        row.createCell(5).setCellValue("下拉字段值");
        row.getCell(5).setCellStyle(cellStyle);
        row.createCell(6).setCellValue("下拉字段示例");
        row.getCell(6).setCellStyle(cellStyle);
        row.createCell(7).setCellValue("日期字段值");
        row.getCell(7).setCellStyle(cellStyle);
        row.createCell(8).setCellValue("日期字段示例");
        row.getCell(8).setCellStyle(cellStyle);
        row.createCell(9).setCellValue("是/否重复校验");
        row.getCell(9).setCellStyle(cellStyle);
        row.createCell(10).setCellValue("部门导入说明");
        row.getCell(10).setCellStyle(cellStyle);
        Integer rownumber = 1;
        Integer rowstart = 1;
        //主表单
        if(null != sysFormDO && null != sysFormDO.getFormJsonUser()){
            SysModelDataVo vo  = createTable(sheet,rownumber,rowstart,sysFormDO,columnList,cellStyle);
            sheet = vo.getSheet();
            rownumber = vo.getRownumber();
            rowstart = vo.getRowstart();
        }
        //子表单
        List<SysFormDO> sysFormChildList = sysFormService.getChildSysFormByIds(sysFormDO.getId());
        if(ObjectUtil.isNotEmpty(sysFormChildList)){
            for(int i = 0;i < sysFormChildList.size(); i++){
                SysFormDO sysform = sysFormChildList.get(i);
                List<SysTableColumn> childColumnList = getColumn(sysform.getTableName());
                SysModelDataVo vo  = createTable(sheet,rownumber,rowstart,sysform,childColumnList,cellStyle);
                sheet = vo.getSheet();
                rownumber = vo.getRownumber();
                rowstart = vo.getRowstart();
            }
        }
        return sheet;
    }

    public SysModelDataVo createTable(XSSFSheet sheet,Integer rownumber,Integer rowstart,SysFormDO sysFormDO,List<SysTableColumn> columnList,XSSFCellStyle cellStyle) {
        for(int i = 0;i < columnList.size(); i++){
            Row rowList = sheet.createRow(rownumber);
            if(ObjectUtil.isNotEmpty(columnList.get(i).getColumnComment()) && !"id".equals(columnList.get(i).getColumnName()) && !"create_time".equals(columnList.get(i).getColumnName()) && !"tenant_id".equals(columnList.get(i).getColumnName()) && !"update_time".equals(columnList.get(i).getColumnName()) && !"data_type".equals(columnList.get(i).getColumnName()) && !"creator".equals(columnList.get(i).getColumnName()) &&!"parent_id".equals(columnList.get(i).getColumnName()) &&!"relation_id".equals(columnList.get(i).getColumnName())&&!"data_id".equals(columnList.get(i).getColumnName()) &&!"version_id".equals(columnList.get(i).getColumnName()) &&!"approve_status".equals(columnList.get(i).getColumnName()) &&!"is_offline".equals(columnList.get(i).getColumnName())){
                rowList.createCell(0).setCellValue(sysFormDO.getFormName());
                rowList.getCell(0).setCellStyle(cellStyle);
                //字段名
                rowList.createCell(1).setCellValue(columnList.get(i).getColumnComment());
                rowList.getCell(1).setCellStyle(cellStyle);
                //类型
                rowList.createCell(2).setCellValue(columnList.get(i).getDataType());
                rowList.getCell(2).setCellStyle(cellStyle);
                //必填（表单侧）
                rowList.createCell(3).setCellValue(IsNullUtil.isnullFormtemp(columnList.get(i).getColumnName(),null,sysFormDO));
                //必填（数据库侧）
                //rowList.createCell(3).setCellValue(columnList.get(i).getIsNullable());
                rowList.getCell(3).setCellStyle(cellStyle);
                rowList.createCell(4).setCellValue(IsNullUtil.defaultForm(columnList.get(i).getColumnName(),sysFormDO));
                rowList.getCell(4).setCellStyle(cellStyle);
                //下拉列表
                rowList.createCell(5).setCellValue(IsNullUtil.selectForm(columnList.get(i).getColumnName(),sysFormDO));
                rowList.getCell(5).setCellStyle(cellStyle);
                //下拉示例
                rowList.createCell(6).setCellValue(IsNullUtil.selectFormSingleOrMultiple(columnList.get(i).getColumnName(),sysFormDO));
                rowList.getCell(6).setCellStyle(cellStyle);
                //日期字段
                rowList.createCell(7).setCellValue(IsNullUtil.dateTypeForm(columnList.get(i).getColumnName(),sysFormDO));
                rowList.getCell(7).setCellStyle(cellStyle);
                //日期示例
                rowList.createCell(8).setCellValue(IsNullUtil.dateTypeFormExample(columnList.get(i).getColumnName(),sysFormDO));
                rowList.getCell(8).setCellStyle(cellStyle);
                //是否数据重复校验
                rowList.createCell(9).setCellValue(IsNullUtil.dataRepeat(columnList.get(i).getColumnName(),null,sysFormDO));
                rowList.getCell(9).setCellStyle(cellStyle);
                //部门说明
                rowList.createCell(10).setCellValue(IsNullUtil.deptForm(columnList.get(i).getColumnName(),sysFormDO));
                rowList.getCell(10).setCellStyle(cellStyle);
                rownumber++;
            }
        }
        if (rowstart != rownumber-1) {
            CellRangeAddress rangenew = new CellRangeAddress(rowstart, rownumber-1, 0, 0);
            sheet.addMergedRegion(rangenew);
        }
        rowstart = rownumber;
        SysModelDataVo modelvo = new SysModelDataVo();
        modelvo.setSheet(sheet);
        modelvo.setRownumber(rownumber);
        modelvo.setRowstart(rowstart);
        return modelvo;
    }

    public static void createDropDownList(Sheet sheet, String[] values, int firstRow, int lastRow, int firstCol, int lastCol) {
        DataValidationHelper helper = sheet.getDataValidationHelper();

        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        // 设置下拉框数据
        DataValidationConstraint constraint = helper.createExplicitListConstraint(values);
        DataValidation dataValidation = helper.createValidation(constraint, addressList);

        // Excel兼容性问题
        if (dataValidation instanceof XSSFDataValidation) {
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setShowErrorBox(true);
        } else {
            dataValidation.setSuppressDropDownArrow(false);
        }

        sheet.addValidationData(dataValidation);
    }

    public static boolean getLabel(SysFormDO sysFormDO,SysTableColumn sysTableColumn,Integer count,Map<Integer,String[]> map){
        Boolean flag = true;
        if(null != sysFormDO && null != sysFormDO.getFormJsonUser()) {
            JSONArray fieldsJsonArray = (JSONArray) JSON.parseObject(sysFormDO.getFormJsonUser()).get("fields");
            for(int j = 0;j< fieldsJsonArray.size();j++){
                JSONObject object= (JSONObject) fieldsJsonArray.get(j);
                if(null != object.get("__vModel__") && (object.get("__vModel__")).equals(sysTableColumn.getColumnName())){
                    if(true == (Boolean) (((JSONObject) object.get("__config__")).get("required"))){
                        sysTableColumn.setRequired(true);
                    }else{
                        sysTableColumn.setRequired(false);
                    }
                    if("my-select".equals(((JSONObject) object.get("__config__")).get("tag")) || "my-radio-group".equals(((JSONObject) object.get("__config__")).get("tag"))){
                        JSONArray jsonArray= (JSONArray) object.get("options");
                        String[] values = new String[jsonArray.size()];
                        for(int h = 0;h < jsonArray.size();h++){
                            values[h] = (String)((JSONObject)jsonArray.get(h)).get("label");
                        }
                        map.put(count,values);
                    }
                    flag = true;
                    break;
                }
                if(j == fieldsJsonArray.size() - 1){
                    flag = false;
                }
            }
        }
        return flag;
    }

    public static String getValue(SysFormDO sysFormDO,String columnName,String cellValue){
        if(null != sysFormDO && null != sysFormDO.getFormJsonUser()) {
            JSONArray fieldsJsonArray = (JSONArray) JSON.parseObject(sysFormDO.getFormJsonUser()).get("fields");
            for(int j = 0;j< fieldsJsonArray.size();j++){
                JSONObject object= (JSONObject) fieldsJsonArray.get(j);
                if(null != object.get("__vModel__") && (object.get("__vModel__")).equals(columnName)){
                    if( null != (((JSONObject) object.get("__config__")).get("tag")) && ("my-select".equals(((JSONObject) object.get("__config__")).get("tag")) || "my-radio-group".equals(((JSONObject) object.get("__config__")).get("tag")))) {
                        JSONArray jsonArray = (JSONArray) object.get("options");
                        for (int h = 0; h < jsonArray.size(); h++) {
                            if (cellValue.equals(((JSONObject) jsonArray.get(h)).get("label"))) {
                                if (((JSONObject) jsonArray.get(h)).get("value") instanceof Integer) {
                                    cellValue = (((JSONObject) jsonArray.get(h)).get("value")).toString();
                                } else {
                                    cellValue = (String) ((JSONObject) jsonArray.get(h)).get("value");
                                }
                            }
                        }
                    }
                }
            }
        }
        return cellValue;
    }

    public static Date getDate(int days) {
        Calendar c = Calendar.getInstance();
        c.set(1900, 0, 1);
        c.add(Calendar.DATE, days - 2);
        return c.getTime();
    }
    public static Date getTime(Date date, double ditNumber) {
        Calendar c = Calendar.getInstance();
        int mills = (int) (Math.round(ditNumber * 24 * 3600));
        int hour = mills / 3600;
        int minute = (mills - hour * 3600) / 60;
        int second = mills - hour * 3600 - minute * 60;
        c.setTime(date);
        c.set(Calendar.HOUR_OF_DAY, hour);
        c.set(Calendar.MINUTE, minute);
        c.set(Calendar.SECOND, second);
        return c.getTime();
    }

    public static String getvehicle (double dd){
        int mills = (int) (Math.round(dd * 24 * 3600));
        int hour = mills / 3600;
        int minute = (mills - hour * 3600) / 60;
        int second = mills - hour * 3600 - minute * 60;
        return hour + ":" + minute + ":" + second;
    }

    public CommonResult intLengVerif (SysFromInsertDataVo sysFromInsertDataVo){
        List<SysFromDataColumnVo> sysFromInsertDataList = sysFromInsertDataVo.getData();
        for (SysFromDataColumnVo vo :sysFromInsertDataList) {
            if(ObjectUtil.isNotEmpty(vo.getType()) && vo.getType().equals("int") && ObjectUtil.isNotEmpty(vo.getValue())){
                try{
                    Integer.parseInt(vo.getValue());
                }catch (NumberFormatException e){
                    return CommonResult.error(500,vo.getName()+"最大值不能超过2147483647");
                }
            }
        }
        return CommonResult.success(null);
    }

    public CommonResult intLengVerifUp (SysFromUpDataVo sysFromUpDataVo){
        if(ObjectUtil.isNotEmpty(sysFromUpDataVo)){
            if(ObjectUtil.isNotEmpty(sysFromUpDataVo.getData())){
                List<SysFromDataColumnVo> sysFromInsertDataList = sysFromUpDataVo.getData();
                for (SysFromDataColumnVo vo :sysFromInsertDataList) {
                    if(ObjectUtil.isNotEmpty(vo.getValue())){
                        if(vo.getType().equals("int")){
                            try{
                                Integer.parseInt(vo.getValue());
                            }catch (NumberFormatException e){
                                return CommonResult.error(500,vo.getName()+"最大值不能超过2147483647");
                            }
                        }
                    }
                }
            }
        }
        return CommonResult.success(null);
    }

    @Override
    public Map<Object, Object> getLinkageData(SysLinkAgeDataVo sysLinkAgeDataVo) {
        CommonResult json = rdbApiService.sourceTestList(Long.parseLong(sysLinkAgeDataVo.getTableName()));
        Map<Object,Object> mappp = (Map<Object, Object>) json.getData();
        List<Map<Object,Object>>  mapList = (List<Map<Object, Object>>) mappp.get("rowMaps");
        for (int i = 0; i < mapList.size(); i++) {
            Map<Object,Object> map = mapList.get(i);
            if(ObjectUtil.isNotEmpty(map)){
                if(String.valueOf(map.get(sysLinkAgeDataVo.getLinkName())).equals(String.valueOf(sysLinkAgeDataVo.getCurValue()))){
                    Map<Object,Object> mapreturn = new HashMap<>();
                    mapreturn.put(sysLinkAgeDataVo.getLinkValueName(),map.get(sysLinkAgeDataVo.getLinkValueName()));
                    return mapreturn;
                }
            }
        }
        return null;
       /* String sql = "SELECT "+ sysLinkAgeDataVo.getLinkValueName() +" FROM "+ sysLinkAgeDataVo.getTableName() +" WHERE "+ sysLinkAgeDataVo.getLinkName() +"= '" + sysLinkAgeDataVo.getCurValue() + "'";
        //适配子表单-关联值与显示值不在一个表单上适用
        //String sql = "SELECT "+ sysLinkAgeDataVo.getLinkValueName() +" FROM "+ sysLinkAgeDataVo.getShowTableName() +" WHERE "+ sysLinkAgeDataVo.getTableName() +"."+ sysLinkAgeDataVo.getLinkName() +"= '" + sysLinkAgeDataVo.getCurValue() + "'";
        List<Map<Object,Object>> map = sysCurrencyMapper.getLinkageData(sql);
        if(ObjectUtil.isNotEmpty(map)){
            String value = (String) AssemblyTypeUtil.selectReplaceValue(sysLinkAgeDataVo.getTableName(),sysLinkAgeDataVo.getLinkValueName(),String.valueOf(map.get(0).get(sysLinkAgeDataVo.getLinkValueName()))).getData();
            map.get(0).put(sysLinkAgeDataVo.getLinkValueName(),value);
            return map.get(0);
        }else{
            return null;
        }*/
    }

    @Override
    public List<SysKeyValueVo> getRelationData(SysLinkAgeDataVo sysLinkAgeDataVo) {
        CommonResult json = rdbApiService.sourceTestList(Long.parseLong(sysLinkAgeDataVo.getTableName()));
        Map<Object,Object> mappp = (Map<Object, Object>) json.getData();
        List<Map<Object,Object>>  mapList = (List<Map<Object, Object>>) mappp.get("rowMaps");
        if(ObjectUtil.isEmpty(mapList)){
            return null;
        }
        List<SysKeyValueVo> sysKeyValueVoList = new ArrayList<>();
        for (int i = 0; i <mapList.size() ; i++) {
            if(String.valueOf(mapList.get(i).get(sysLinkAgeDataVo.getLinkName())).equals(sysLinkAgeDataVo.getCurValue())){
                for (int j = 0; j <sysLinkAgeDataVo.getSysRelationDataList().size() ; j++) {
                    Set<Object> keySet = mapList.get(i).keySet();
                    for (Object key:keySet) {
                        if(key.equals(sysLinkAgeDataVo.getSysRelationDataList().get(j).getSourceFieldName())){
                            SysKeyValueVo sysKeyValueVo = new SysKeyValueVo();
                            sysKeyValueVo.setKey(sysLinkAgeDataVo.getSysRelationDataList().get(j).getFormFieldName());
                            String value = String.valueOf(mapList.get(i).get(key));
                            sysKeyValueVo.setValue(value);
                            sysKeyValueVoList.add(sysKeyValueVo);
                        }
                    }
                }
            }
        }
        return sysKeyValueVoList;
    }

    @Override
    public List<Map<Object,Object>> getCascadeData(SysCascadeVo sysCascadeVo) {
        CommonResult json = rdbApiService.sourceTestList(Long.parseLong(sysCascadeVo.getTableName()));
        Map<Object,Object> mappp = (Map<Object, Object>) json.getData();
        List<Map<Object,Object>>  mapall = (List<Map<Object, Object>>) mappp.get("rowMaps");
        List<Map<Object,Object>> newList = new ArrayList<>();
        if(ObjectUtil.isNotEmpty(mapall)){
            for (int i = 0; i <mapall.size() ; i++) {
                if (!newList.contains(mapall.get(i))) { // 如果新集合中不存在则插入
                    Map<Object,Object> value = new HashMap<>();
                    value.put("label",mapall.get(i).get(sysCascadeVo.getShowName()));
                    value.put("value",mapall.get(i).get(sysCascadeVo.getTableColumn()));
                    newList.add(value);
                }
            }
        }
        return newList;
    }

    @Override
    public String catascadeValue(String sql) {
        return sysCurrencyMapper.catascadeValue(sql);
    }

    @Override
    public Map<Object, Object> fromSubmitCheck(SysFieldFormListVo sysFieldFormListVo ,String tableName) {
        String sql = "SELECT * FROM "+ tableName +" WHERE "+ sysFieldFormListVo.getFiledName() +"= '" + sysFieldFormListVo.getValue() + "'";
        List<Map<Object,Object>> object = sysCurrencyMapper.getRelationData(sql);
        return object.get(0);
    }

    @Override
    public CommonResult formFieldRules(SysFormFieldRulesVo sysFormFieldRulesVo) {
        Boolean tworules = false;
        for (SysFieldRulesListVo rulesList : sysFormFieldRulesVo.getSysFieldRulesListVo()) {
            Boolean onerules = false;
            //Boolean onefrist = false;
            for (SysFieldRuleVo rule : rulesList.getSysFieldRuleVo()) {
                Boolean rules = RuleUtil.newRule(rule,sysFormFieldRulesVo.getFormList());
                onerules = RuleUtil.sumRule(rules,rule.getRelat(),onerules);
                //onefrist = rules;
            }
            tworules = RuleUtil.sumRule(onerules,rulesList.getRelat(),tworules);
        }
        SysFieldRuleReturnVo sysFieldRuleReturnVo = new SysFieldRuleReturnVo();
        if(tworules){
            //规则增加 只  就  区分
            //显示只
            if(sysFormFieldRulesVo.getOnlyHidden()== 0 && ObjectUtil.isNotEmpty(sysFormFieldRulesVo.getDispalyField())){
                sysFieldRuleReturnVo.setDispalyField(sysFormFieldRulesVo.getDispalyField());
                return CommonResult.success("符合规则",sysFieldRuleReturnVo);
            }
            //隐藏只
            if(sysFormFieldRulesVo.getOnlyHidden()== 0 && ObjectUtil.isNotEmpty(sysFormFieldRulesVo.getHiddenField())){
                String displayColum = "";
                List<String> fileNames = Arrays.asList(sysFormFieldRulesVo.getHiddenField().split(","));
                for(SysFieldFormListVo vo:sysFormFieldRulesVo.getFormList()){
                    for (int i = 0;i <fileNames.size() ; i++) {
                        if(vo.getFiledName().equals(fileNames.get(i))){
                            break;
                        }
                        if(!vo.getFiledName().equals(fileNames.get(i)) && i == fileNames.size() -1) {
                            displayColum = displayColum + vo.getFiledName() + ",";
                        }
                    }
                }
                sysFieldRuleReturnVo.setDispalyField(displayColum);
                return CommonResult.success("符合规则",sysFieldRuleReturnVo);
            }
            //显示只+隐藏只
            if(sysFormFieldRulesVo.getOnlyHidden()== 0 && ObjectUtil.isNotEmpty(sysFormFieldRulesVo.getDispalyField()) && sysFormFieldRulesVo.getOnlyHidden()== 0 && ObjectUtil.isNotEmpty(sysFormFieldRulesVo.getHiddenField())){
                sysFieldRuleReturnVo.setDispalyField(sysFormFieldRulesVo.getDispalyField());
                return CommonResult.success("符合规则",sysFieldRuleReturnVo);
            }
            //显示就
            if(sysFormFieldRulesVo.getOnlyDisplay()==1 && sysFormFieldRulesVo.getOnlyHidden()==0 && ObjectUtil.isNotEmpty(sysFormFieldRulesVo.getDispalyField())){
                String displayColum = "";
                for(SysFieldFormListVo vo:sysFormFieldRulesVo.getFormList()){
                    displayColum = displayColum + vo.getFiledName() + ",";
                }
                sysFieldRuleReturnVo.setDispalyField(displayColum);
                return CommonResult.success("符合规则",sysFieldRuleReturnVo);
            }
            //隐藏就
            if(sysFormFieldRulesVo.getOnlyDisplay()==0 && sysFormFieldRulesVo.getOnlyHidden()==1 && ObjectUtil.isNotEmpty(sysFormFieldRulesVo.getHiddenField())){
                String displayColum = "";
                List<String> fileNames = Arrays.asList(sysFormFieldRulesVo.getHiddenField().split(","));
                for(SysFieldFormListVo vo:sysFormFieldRulesVo.getFormList()){
                    for (int i = 0;i <fileNames.size() ; i++) {
                        if(vo.getFiledName().equals(fileNames.get(i))){
                            break;
                        }
                        if(!vo.getFiledName().equals(fileNames.get(i)) && i == fileNames.size() -1) {
                            displayColum = displayColum + vo.getFiledName() + ",";
                        }
                    }
                }
                sysFieldRuleReturnVo.setDispalyField(displayColum);
                return CommonResult.success("符合规则",sysFieldRuleReturnVo);
            }
            //隐藏就+显示就
            if(sysFormFieldRulesVo.getOnlyHidden()==1  && sysFormFieldRulesVo.getOnlyDisplay()==1){
                String displayColum = "";
                List<String> fileNames = Arrays.asList(sysFormFieldRulesVo.getHiddenField().split(","));
                for(SysFieldFormListVo vo:sysFormFieldRulesVo.getFormList()){
                    if(ObjectUtil.isNotEmpty(sysFormFieldRulesVo.getHiddenField())){
                        for (int i = 0;i <fileNames.size() ; i++) {
                            if(vo.getFiledName().equals(fileNames.get(i))){
                                break;
                            }
                            if(!vo.getFiledName().equals(fileNames.get(i)) && i == fileNames.size() -1) {
                                displayColum = displayColum + vo.getFiledName() + ",";
                            }
                        }
                    }else{
                        displayColum = displayColum + vo.getFiledName() + ",";
                    }
                }
                sysFieldRuleReturnVo.setDispalyField(displayColum);
                return CommonResult.success("符合规则",sysFieldRuleReturnVo);
            }
        }
        return CommonResult.success("没有符合规则",null);
    }

    @Override
    public List<Map<String,Object>> getDistanceData(String disTableName, String disFieldName,String dislinkName) {
        String sql = "select table_name from information_schema.tables where table_schema='"+tableSchema+"';";
        List<String> tableNameLIst = sysCurrencyService.getTableNameList(sql);
        if(tableNameLIst.contains(disTableName)){
            String sqlONe = "SELECT "+ dislinkName +","+ disFieldName +" FROM "+ disTableName;
            List<Map<String,Object>> sysDistanceDataVoList = sysCurrencyMapper.getDistanceData(sqlONe);
            return sysDistanceDataVoList;
        }
        List<Map<String,Object>> sysDistanceDataVoList = new ArrayList<>();
        return sysDistanceDataVoList;
    }

    @Override
    public List<String> getTableNameListForm(String sql) {
        List<SysFormDO> sysFormDOList = sysFormService.selectListAll();
        List<String> tableNameList = sysCurrencyMapper.getTableNameList(sql);
        for (SysFormDO vo:sysFormDOList) {
            if(tableNameList.contains(vo.getTableName())){
                tableNameList.remove(vo.getTableName());
            }
        }
        return tableNameList;
    }

    @Override
    public CommonResult updatatablecolumn(SysFromTableVo sysFromVo) {
        List<SysFromTableColumnVo> SysFromTableColumnList= sysFromVo.getData();
        if(ObjectUtil.isNotEmpty(SysFromTableColumnList)){
            for (int i = 0; i <SysFromTableColumnList.size() ; i++) {
                SysFromTableColumnVo vo = SysFromTableColumnList.get(i);
                if(ObjectUtil.isNotEmpty(vo.getNewName())){
                    String sqldelte = "ALTER TABLE "+ sysFromVo.getTableName() +" DROP "+ vo.getName() +"";
                    sysCurrencyMapper.createData(sqldelte);
                }else{
                    return CommonResult.success("原始字段名必填!");
                }
                String sqlcreate = "ALTER TABLE "+ sysFromVo.getTableName() +" ADD COLUMN ";
                if(ObjectUtil.isNotEmpty(vo.getNewName())){
                    sqlcreate = sqlcreate + "`" + vo.getNewName() + "` ";
                }else{
                    return CommonResult.success("字段名必填!");
                }
                if(ObjectUtil.isNotEmpty(vo.getFormat())){
                    sqlcreate = sqlcreate + vo.getFormat() + "(" + vo.getLength() + ")";
                }else{
                    return CommonResult.success("字段类型必填!");
                }
                if(ObjectUtil.isNotEmpty(vo.getDesc())){
                    sqlcreate = sqlcreate + " COMMENT '" + vo.getDesc() + "'";
                }else{
                    return CommonResult.success("字段名必填!");
                }
                sysCurrencyMapper.createData(sqlcreate);
            }
        }
        return CommonResult.success("创建成功",true);
    }

    /**
     * 解析XLS的图片
     *
     * @param workbook   workbook
     * @param sheetIndex 第几页sheet的图片
     * @return
     */
    private static Map<String, Map<Integer, PictureData>> parseSheetPicForXLS(HSSFWorkbook workbook, int sheetIndex, Map<Integer, String> keyMap) {
        //初始化结果
        Map<String, Map<Integer, PictureData>> result = new HashMap<>();
        //获取workbook所有图片列表
        List<HSSFPictureData> pictures = workbook.getAllPictures();
        //获取对应sheet
        HSSFSheet sheet = workbook.getSheetAt(sheetIndex);
        //如果存在图片
        if (pictures.size() > 0) {
            //获取列表
            List<HSSFShape> children = sheet.getDrawingPatriarch().getChildren();
            //循环
            for (HSSFShape shape : children) {
                //获取并强转
                HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
                //如果是
                if (shape instanceof HSSFPicture) {
                    //强转
                    HSSFPicture pic = (HSSFPicture) shape;
                    //从workbook所有图片中找到该图片
                    HSSFPictureData picData = pictures.get(pic.getPictureIndex() - 1);
                    //如果存在改key
                    if (keyMap.containsKey(anchor.getCol1())) {
                        //获取key
                        String key = keyMap.get(anchor.getCol1());
                        //尝试获取mep
                        Map<Integer, PictureData> map = result.getOrDefault(key, new HashMap<>());
                        //组装
                        map.put(anchor.getRow1() - 1, picData);
                        result.put(key, map);
                    }
                }
            }

        }
        //返回
        return result;
    }

    /**
     * 解析XLSX图片
     *
     * @return
     */
    private static Map<String, Map<Integer, PictureData>> parseSheetPicForXLSX(XSSFWorkbook workbook, int sheetIndex, Map<Integer, String> keyMap) {
        //初始化结果
        Map<String, Map<Integer, PictureData>> result = new HashMap<>();
        //获取列表
        List<POIXMLDocumentPart> relationList = workbook.getSheetAt(sheetIndex).getRelations();
        //循环1
        for (POIXMLDocumentPart dr : relationList) {
            //如果是类型
            if (dr instanceof XSSFDrawing) {
                //强转
                XSSFDrawing drawing = (XSSFDrawing) dr;
                //获取列表
                List<XSSFShape> shapeList = drawing.getShapes();
                //循环2
                for (XSSFShape shape : shapeList) {
                    //强转
                    XSSFPicture pic = (XSSFPicture) shape;
                    //获取其可能的表格
                    CTMarker ctMarker = pic.getPreferredSize().getFrom();
                    //如果存在改key
                    if (keyMap.containsKey(ctMarker.getCol())) {
                        //获取key
                        String key = keyMap.get(ctMarker.getCol());
                        //尝试获取结果map
                        Map<Integer, PictureData> map = result.getOrDefault(key, new HashMap<>());
                        //组装
                        map.put(ctMarker.getRow() - 1, pic.getPictureData());
                        result.put(key, map);
                    }
                }
            }
        }
        //返回
        return result;
    }

    @Override
    public CommonResult getDataSunRemoval(String tableName, String fieldName,String type) {
        String sql = "";
        if(type.equals("SUM")){
            sql = "SELECT SUM(num) AS total FROM (SELECT COUNT(1) AS num FROM "+ tableName +" where "+ fieldName +" is not null and "+ fieldName +" !='' GROUP BY "+ fieldName +") T;";
        }else if(type.equals("REM")){
            sql = "SELECT COUNT(1) AS sun FROM (SELECT COUNT(1) AS num FROM "+ tableName +"  where "+ fieldName +" is not null and "+ fieldName +" !='' GROUP BY "+ fieldName +") T;";
        }
        return CommonResult.success("获取成功",sysCurrencyMapper.getDataSunRemoval(sql));
    }

    @Override
    public void vesDataPocDownload(List<String> vesList, HttpServletResponse response) {
        if (ObjectUtil.isNotEmpty(vesList)) {
            String config = getMinioConfig();
            JSONObject jsonObject =  JSON.parseObject(config);
            String endpoint = jsonObject.getString("endpoint");
            String regEx="((http|ftp|https)://)(([a-zA-Z0-9._-]+)|([0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}))(([a-zA-Z]{2,6})|(:[0-9]{1,4})?)";
            List<Map<String, String>> filePaths = new ArrayList<>();
            for (int i = 0; i <vesList.size() ; i++) {
                //SysVesDataVo ves = vesList.get(i);
                Map<String, String> map = new HashMap<>();
                JSONArray jsonArray = JSON.parseArray(vesList.get(i));
                for(int j = 0; j < jsonArray.size(); j++){
                    JSONObject jsonObj = jsonArray.getJSONObject(j);
                    map.put("fileName",jsonObj.get("name").toString());
                    String url = jsonObj.get("url").toString().replaceAll(regEx,endpoint);
                    map.put("filePath",url);
                    logger.info("url-------------------------------"+url);
                    filePaths.add(map);
                }
            }
            String zipName = "POC_" + ((int) (Math.random() * 10000)) + ".zip";
            String zipPath =  uploadFolder + zipName;
            //zipPath =  "D:/test/" + zipName;
            CompressUtil.compress(filePaths, zipPath, false,uploadFolder);
            File pocZipFile = new File(zipPath);
            CompressUtil.downloadZip(response, zipName, pocZipFile);
        }
    }

    @Override
    public void vesDataPocDownloadAll(String tableName, List<String> fieldNameList, HttpServletResponse response) {
        String config = getMinioConfig();
        JSONObject jsonObject =  JSON.parseObject(config);
        String endpoint = jsonObject.getString("endpoint");
        String regEx="((http|ftp|https)://)(([a-zA-Z0-9._-]+)|([0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}.[0-9]{1,3}))(([a-zA-Z]{2,6})|(:[0-9]{1,4})?)";
        List<String> vesList = new ArrayList<>();
        String sql = "SELECT * FROM "+ tableSchema + "." + tableName + " ORDER BY create_time DESC,id DESC;";
        List<Map<String,Object>> dataMapList = sysCurrencyMapper.selectData(sql);
        for (int i = 0; i <dataMapList.size() ; i++) {
            Map<String,Object> map = dataMapList.get(i);
            map.forEach((key,value) ->{
                if(fieldNameList.contains(key)){
                    if(!"\"\"".equals(value) && !"[]".equals(value)){
                        vesList.add(String.valueOf(value));
                    }
                }
            });
        }
        if (ObjectUtil.isNotEmpty(vesList)) {
            List<Map<String, String>> filePaths = new ArrayList<>();
            for (int i = 0; i <vesList.size() ; i++) {
                String ves = vesList.get(i);
                Map<String, String> map = new HashMap<>();
                JSONArray jsonArray = JSON.parseArray(ves);
                for(int j = 0; j < jsonArray.size(); j++){
                    JSONObject jsonObj = jsonArray.getJSONObject(j);
                    map.put("fileName",jsonObj.get("name").toString());
                    String url = jsonObj.get("url").toString().replaceAll(regEx,endpoint);
                    map.put("filePath",url);
                    logger.info("url-------------------------------"+url);
                    filePaths.add(map);
                }
            }
            String zipName = "POC_" + ((int) (Math.random() * 10000)) + ".zip";
            String zipPath =  uploadFolder + zipName;
            CompressUtil.compress(filePaths, zipPath, false,uploadFolder);
            File pocZipFile = new File(zipPath);
            CompressUtil.downloadZip(response, zipName, pocZipFile);
        }
    }

    @TenantIgnore // 初始化缓存，无需租户过滤
    public String getMinioConfig() {
        String sqlconfig = "SELECT config FROM infra_file_config WHERE name = 'minio'";
        String config = sysCurrencyMapper.getMinio(sqlconfig);
        return config;
    }

    @Override
    public CommonResult isNullFile(String tableName, List<String> fieldNameList, HttpServletResponse response) {
        List<String> vesList = new ArrayList<>();
        String sql = "SELECT * FROM "+ tableName + " ORDER BY create_time DESC,id DESC;";
        List<Map<String,Object>> dataMapList = sysCurrencyMapper.selectData(sql);
        for (int i = 0; i <dataMapList.size() ; i++) {
            Map<String,Object> map = dataMapList.get(i);
            map.forEach((key,value) ->{
                if(fieldNameList.contains(key)){
                    if(!"\"\"".equals(value) && !"[]".equals(value)){
                        vesList.add(String.valueOf(value));
                    }
                }
            });
        }
        if (ObjectUtil.isNotEmpty(vesList)) {
            return CommonResult.success("200",200);
        }
        return CommonResult.success("500",null);
    }

    @Override
    public List<Map<String, Object>> isDataRepeat(String sql) {
        return sysCurrencyMapper.isDataRepeat(sql);
    }

    @Override
    public String getDataId(String sql) {
        return sysCurrencyMapper.getDataId(sql);
    }

    @Override
    public String getCreator(String sql) {
        return sysCurrencyMapper.getCreator(sql);
    }

    @Override
    public CommonResult excelInputChildForm(MultipartFile file,String tableName) throws Exception {
        String type = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf("."));
        InputStream in = file.getInputStream();
        Workbook workbook;
        //判断后缀 决定使用的解析方式，决定如何创建具体的对象
        if (".xls".equals(type)) {
            //2003版的解析方式
            workbook = new HSSFWorkbook(in);
        } else if (".xlsx".equals(type)) {
            //2007
            workbook = new XSSFWorkbook(in);
        } else {
            //未知内容
            return CommonResult.success("文件格式不正确");
        }
        List<SysExcelTable> tableLIst = new ArrayList<>();
        Sheet sheetAt = workbook.getSheetAt(0);
        List<SysRowTable> sysRowTableList = new ArrayList<>();
        int rowNumber = sheetAt.getPhysicalNumberOfRows();
        if (rowNumber > platformRowNumber) {
            return CommonResult.error(500, "数据已超过10000行");
        }
        //表单结构form
        SysFormDO sysFormDO = sysFormService.selectOneByTableName(tableName);
        //表单字段集合
        List<SysTableColumn> tableColumnList = getColumn(tableName);
        //主表表信息
        List<SysInDataVo> dataList = new ArrayList<>();
        List<SysIfDataVo> sysIfDataVoAll = new ArrayList();
        for (int i = 0; i < sheetAt.getPhysicalNumberOfRows(); i++) {
            Row row = sheetAt.getRow(i);
            if(i == 0){
                for (int index = 0; index < row.getPhysicalNumberOfCells(); index++) {
                    Cell cell = row.getCell(index);
                    for (int j = 0; j < tableColumnList.size(); j++) {
                        if (ObjectUtil.isNotEmpty(cell)) {
                            if (cell.getStringCellValue().equals(tableColumnList.get(j).getColumnComment())) {
                                SysInDataVo columnVo = new SysInDataVo();
                                columnVo.setCell(index);
                                columnVo.setFildName(tableColumnList.get(j).getColumnName());
                                columnVo.setDataType(tableColumnList.get(j).getDataType());
                                columnVo.setComment(tableColumnList.get(j).getColumnComment());
                                columnVo.setIsNull(tableColumnList.get(j).getIsNullable());
                                dataList.add(columnVo);
                                tableColumnList.remove(tableColumnList.get(j));
                            }
                        }
                    }
                }
            }else{
                List<SysFromDataColumnVo> columnList = new ArrayList<>();
                for (int k = 0; k < dataList.size(); k++) {
                    Cell cell = row.getCell(k);
                    if(ObjectUtil.isNotEmpty(cell)){
                        SysFromDataColumnVo sysFromDataColumnVo = new SysFromDataColumnVo();
                        sysFromDataColumnVo.setName(dataList.get(k).getFildName());
                        sysFromDataColumnVo.setType(dataList.get(k).getDataType());
                        sysFromDataColumnVo.setIsnullable(dataList.get(k).getIsNull());
                        sysFromDataColumnVo.setComment(dataList.get(k).getComment());
                        String value = CellUtil.getCellValueByCell(cell);
                        sysFromDataColumnVo.setValue(value);
                        columnList.add(sysFromDataColumnVo);
                    }
                }
                SysIfDataVo sysIfDataChild = excelDataRule(columnList,sysFormDO);
                sysIfDataVoAll.add(sysIfDataChild);
            }
        }
        return CommonResult.success("获取成功",sysIfDataVoAll);
    }

    @Override
    public CommonResult getVesion(String tableName,Long id) throws Exception {
        List<AdminUserRespDTO> list = adminUserService.selectListAll();
        List<SysVesionVo> sysVesionVoList = new ArrayList<>();
        String sql = "SELECT * FROM " + tableSchema + "." + tableName + " WHERE id='"+ id + "'";
        Map<Object, Object> map = sysCurrencyMapper.getDataOne(sql);
        String sqldata = "SELECT * FROM " + tableSchema + "." + tableName + " WHERE data_id='"+ map.get("data_id") + "' ORDER BY version_id desc";
        List<Map<String,Object>> mapList = sysCurrencyMapper.selectVersionData(sqldata);
        mapList = AssemblyTypeUtil.associatedDataMapping(tableName,mapList,null).getData();
        //子表单插入
        SysFormDO sysForm = sysFormService.selectOneByTableName(tableName); //主表
        List<SysFormDO> sysFormList = sysFormService.getChildSysFormByIds(sysForm.getId()); //字表
        for (int i = 0; i <mapList.size() ; i++) {
            CommonResult<BpmProcessInstanceStatusDTO> instanceStatus = processInstanceApi.getInstanceStatus(sysForm.getId().toString(), mapList.get(i).get("id").toString());
            BpmProcessInstanceStatusDTO checkedData = instanceStatus.getCheckedData();
            if (checkedData != null) {
                mapList.get(i).put("result", checkedData.getResult() == null ? 4 : checkedData.getResult());
                mapList.get(i).put("processInstanceId", checkedData.getProcessInstanceId());
                mapList.get(i).put("formStatus", checkedData.getFormStatus());
                mapList.get(i).put("processDefinitionId", checkedData.getProcessDefinitionId());
            }
        }
        for (int i = 0; i <mapList.size() ; i++) {
            for (int j = 0; j <list.size() ; j++) {
                if(String.valueOf(mapList.get(i).get("creator")).equals(String.valueOf(list.get(j).getId()))){
                    mapList.get(i).put("creator",list.get(j).getNickname());
                }
            }
            List<Map<String,Object>> mapchilddata = new ArrayList<>();
            List<Map<String,Object>> childTables = new ArrayList<>();
            for (SysFormDO formDO : sysFormList) {
                String sqlChildSeach = "SELECT  * from " + tableSchema + "." + formDO.getTableName() + " WHERE parent_id = " + mapList.get(i).get("data_id") + " and version_id = " + mapList.get(i).get("version_id");
                mapchilddata = sysCurrencyMapper.selectVersionData(sqlChildSeach);
                mapchilddata = AssemblyTypeUtil.associatedDataMapping(formDO.getTableName(),mapchilddata,null).getData();
                Map<String,Object> childTable = new HashMap<>();
                childTable.put("tableName", formDO.getTableName());
                childTable.put("data", JSON.toJSONString(mapchilddata));
                childTables.add(childTable);
            }
            SysVesionVo sysVesionVo = new SysVesionVo();
            sysVesionVo.setChildTables(childTables);
            sysVesionVo.setTableData(mapList.get(i));
            sysVesionVoList.add(sysVesionVo);
        }
        return CommonResult.success("获取成功",sysVesionVoList);
    }

    @Override
    public CommonResult getJsData(String tableName, String fieldName, String fieldType,String value) throws Exception {
        SysFromInsertDataVo sysFromInsertDataVo = new SysFromInsertDataVo();
        //判断字段类型拼接sql  sql = SqlUtil.insertIntoData(sysFromInsertDataVo,sql);
        String sql = "SELECT  * from " + tableName + " WHERE " + fieldName + " = '" + value +"'";
        List<Map<String,Object>> dataList = sysCurrencyMapper.selectData(sql);
        return CommonResult.success("获取成功",dataList);
    }
}
